Mysql query help again (PHP).?

Question

Okay, i dont understand how to draw/echo a table of all the users and their info. I have this so far:

$sql = 'SELECT * FROM `3dmark06` ORDER BY `3dmark06`.`score` DESC LIMIT 0, 30 ';

I want to have it create a HTML table and add a row in that HTML table for each user with their info. So you know what the variables are and their fields etc here is my code to create an entry but i left out the values part on purpose.

INSERT INTO 3dmark06 (user, score, cpu, cpuspeed, gpu, gpuspeed, ram, os, link1, link2, link3, activate)

Lastly, i dont want activate shown, but rather, if its value is 0 then dont include it in the table. Thanx to anyone who helps me out.

Answer 1

SELECT user, score, cpu, cpuspeed, gpu, gpuspeed, ram, os, link1, link2, link3 FROM 3dmark06 WHERE activate = 1

ORDER BY score DESC



Never use SELECT * because you"ll never know the order of the columns that way (and even if you do, you"re confining yourself to the current set, not being able to add or remove without breaking the HTML table...



Kudos to Steve for picking up what i totally missed... thumbs up. :-)

Answer 2

Well, there are two parts to this question. The first part is to devise a query that meets your conditions. I'd explicitly list the fields I wanted returned, and set a WHERE clause to limit what comes back.



$sqlquery = "SELECT user, score, cpu, cpuspeed, gpu, gpuspeed, ram, os, link1, link2, link3

FROM 3dmark06

WHERE activate!=0

ORDER BY score DESC

LIMIT 0,30";





The next part of the question is displaying it. It kind of depends on which database libraries you use -- I generally use the PEAR DB libraries -- if you use something different your database calls may be different. I'm assuming you've opened a database and the object is $db.



$result = $db->query($sqlquery);

$rows = $result->numRows();

echo "\n";

for ($row = 0; $row < $rows; $row++)

{

$datarow = $result->fetchRow();

echo "";

echo "";

echo "";

echo "";

echo "";

echo "";

echo "";

echo "";

echo "";

echo "";

echo "";

echo "\n";

}

echo "

" . $datarow['user'] . "

" . $datarow['score'] . "

" . $datarow['cpu'] . "

" . $datarow['cpuspeed'] . "

" . $datarow['gpu'] . "

" . $datarow['ram'] . "

" . $datarow['os'] . "

" . $datarow['link1'] . "

" . $datarow['link2'] . "

" . $datarow['link3'] . "

\n";





You get the idea. You may want to customize the html so that it fits your needs and add in some error checking. I'd probably use a template instead of embedded echoes, but this works in a quick and dirty way.

Answer 3

here's what you need



http://w3schools.com/php/php_mysql_select.asp