CODEWITHSUNDEEP
phpmysql
John
John

Reputation:

Retrieving values from several tables in a MySQL database

I have a MySQL database called "bookfeather" with several tables that contain list books. Under each table, each book has a given number of votes. The PHP code below allows the user to enter in a book title ($entry), and then returns the total number of votes that book has in all tables ($sum).

How could I use PHP to make a 2-column, 25-row table that lists the 25 books in the database with the highest value for $sum (in descending order)?

Thanks in advance,

John

mysql_connect("mysqlv10", "username", "password") or die(mysql_error());
mysql_select_db("bookfeather") or die(mysql_error());

// We preform a bit of filtering

$entry = strip_tags($entry);
$entry = trim ($entry);
$entry = mysql_real_escape_string($entry);


$result = mysql_query("SHOW TABLES FROM bookfeather") 
or die(mysql_error()); 

$table_list = array();
while(list($table)= mysql_fetch_row($result))
{
  $sqlA = "SELECT COUNT(*) FROM `$table` WHERE `site` LIKE '$entry'";

  $resA = mysql_query($sqlA) or die("$sqlA:".mysql_error());
  list($isThere) = mysql_fetch_row($resA);
  $isThere = intval($isThere);
  if ($isThere)
  {
     $table_list[] = $table;
  }

}

//$r=mysql_query("SELECT * , votes_up - votes_down AS effective_vote FROM `$table[0]` ORDER BY effective_vote DESC");


if(mysql_num_rows($resA)>0){
foreach ($table_list as $table) { 
    $sql = "SELECT votes_up FROM `$table` WHERE `site` LIKE '$entry'"; 
    $sql1 = mysql_query($sql) or die("$sql:".mysql_error());
   while ($row = mysql_fetch_assoc($sql1)) {
       $votes[$table] = $row['votes_up'];
       $sum += $row['votes_up'];
       //echo $table . ': "' . $row['votes_up'] . " for $entry from $table\"<br />";
   } 

}
}
else{
print "<p class=\"topic2\">the book \"$entry\" has not been added to any category</p>\n";
}


//within your loop over the DB rows
//$votes[$table] = $row['votes_up'];

//afterwards

if($sum>0){



print "<table class=\"navbarb\">\n";
print "<tr>";
print "<td class='sitenameb'>".'<a type="amzn" category="books" class="links2b">'.$entry.'</a>'."</td>";
print "</tr>\n";
print "</table>\n";

//echo "<p class=\"topic3\">".'<a href="http://'.$entry.'" class="links3">'.$entry.'</a>'. "</p>\n";
echo "<p class=\"topic4\">". number_format($sum) . ' votes in total.'."</p>\n";

Upvotes: 2

Views: 344

Answers (2)

davidisinaband
davidisinaband

Reputation: 70

I did something like this recently. In your database, you'll have to rename each field to a corresponding book name.php like (TaleofTwoCities.php). Now on your page that will display the vote results, you'll need to include some php files that will drive the database query on each load. I called mine "engine1.php" and "engine2.php." These will do all your sorting for you.

$query1 = mysql_fetch_row(mysql_query("SELECT url FROM pages ORDER BY counter DESC
LIMIT 0,1"));
$query2 = mysql_fetch_row(mysql_query("SELECT url FROM pages ORDER BY counter DESC
LIMIT 1,1"));
$query3 = mysql_fetch_row(mysql_query("SELECT url FROM pages ORDER BY counter DESC
LIMIT 2,1"));

and so on.. then..

$num1 = "$query1[0]";
$num2 = "$query2[0]";
$num3 = "$query3[0]";

That part sorts your listings by the number of votes from highest to lowest, with url, in your case, being the name of the books(remember you want it to end in .php - you'll see why in a second), and counter being the field that logs your votes.

Make your second engine.php file and add something like this:

$vquery1 = mysql_fetch_row(mysql_query("SELECT counter FROM pages WHERE 
url='book1.php'"));
$vquery2 = mysql_fetch_row(mysql_query("SELECT counter FROM pages WHERE 
url='book2.php'"));

$vnum1 = "$vquery1[0]";
$vnum2 = "$vquery2[0]";

and so on... Until you get to 25 for both this and engine 1.

Now, in your results page, after you put in the require_once(engine.php) and require_once(engine2.php) at the start of your body, start an HTML table. You only want two columns, so it'll be something like..

<table border=1 cellspacing=0 cellpadding=0>
<tr>
<?php include $num1; ?>
</tr>
<tr>
<?php include $num2; ?>
</tr>

And so on... By naming your field with "book1.php" and including the engines, $num1 will change to a different .php file depending on votes from high to low. Now all you have to do is make small php files for each book like so - no headers or anything because you're inserting it into the middle of html code already:

<td style="width:650px;"><center><img src="images/book1.jpg" alt="" border="none"
/></a></center></td>
<td style="width:150px;">Votes:&nbsp;<?php echo $vnum1;?></td>

And there you have it. A code that will dynamically give you results from high to low depending on the number of votes each book has.

Upvotes: 1

Justin Giboney
Justin Giboney

Reputation: 3301

Try something like this. All of this hasn't been tested so please add comments for changes. I'll work with you to get the code right.

// After getting your array of tables formated like 
$tableArray = array("`tableA`", "`tableB`", "`tableC`");

// create a table statement
$tableStatement = implode(", ", $tableArray);

// create a join statement
$joinStatement = "";
for ($i = 1; $i < count($tableArray); $i++) {
    if ($joinStatement != "")
        $joinStatement .= " AND "; 

    $joinStatement .= $tableArray[0] . ".site = " . $tableArray[$i] . ".site"
}

$firstTable = $tableArray[0];

$sql = "SELECT SUM(votes_up) FROM " . $tableStatement . " WHERE " . $joinStatement . " AND " . $firstTable . ".site LIKE '" . $entry . "' GROUP BY " . $firstTable . ".site ORDER BY SUM(votes_up) DESC";

Edit -------- I now realize that the query above won't work perfectly because votes_up will be ambiguous. Also because you probably want to be doing joins that grab records that are only in one table. I think the concept is the right direction even though the query may not be perfect.

You can do something like $selectStatement = "SUM(tableA.votes_up) + SUM(tableB.votes_up) as total_votes_up"

Upvotes: 1

Related Questions