Display results in table

[hoponhiggo]

Hi

 

I have the following code:

 

$result = mysql_query("SELECT * FROM xbox_games order by gameid");
	$row = mysql_fetch_assoc($result);
	$id = $row["gameid"];
	$title = $row["gametitle"];
	$cover = $row["cover"];

                                <?php echo $title;?>

 

Which displays only the first result from my database.

 

How can i change this to display all the results either as a list, or in a table?

 

Thanks

[gizmola]

Make the fetching into a while loop.  mysql_fetch_assoc.  This is shown in the examples of the manual page for mysql_fetch_row, which mysql_fetch_assoc is an alias for with a parameter that only returns the values key'd by the column name.

[hoponhiggo]

Ok

 

So, i need to make it more like:

 

$result = mysql_query("SELECT * FROM xbox_games order by gameid");
	while ($row = mysql_fetch_assoc($result));
	$id = $row["gameid"];
	$title = $row["gametitle"];
	$cover = $row["cover"];

 

?

[Tonic-_-]

Ok

 

So, i need to make it more like:

 

$result = mysql_query("SELECT * FROM xbox_games order by gameid");
	while ($row = mysql_fetch_assoc($result));
	$id = $row["gameid"];
	$title = $row["gametitle"];
	$cover = $row["cover"];

 

?

 

$result = mysql_query("SELECT * FROM xbox_games order by gameid");
	while ($row = mysql_fetch_assoc($result))
                {
	    $id = $row["gameid"];
      	    $title = $row["gametitle"];
	    $cover = $row["cover"];
                    echo $title . "<br />";
                }

 

Not suppose to add ; next to while()

Another thing don't forget if you're not printing the data within the while statement you will keep overwriting the $id, $title, and $cover variable. So if you just want to build an array list first then I suggest to load those into an array like

 

$array = array();

$array[id] = $row['gameid'];

$array[title] = $row['gametitle'];

$array[cover] = $row['cover'];

 

[hoponhiggo]

Ok

 

So if i then wanted to echo, would it be:

 

echo $array[title]

 

Thanks for your help

[Tonic-_-]

Well the syntax fix that I posted automatically echo's the $title variable, I was just suggesting if you don't want to echo / print that result within the while statement that you should load each game into a array and $array[xxxx][] can do it.

 

Hope you get it working though

[hoponhiggo]

Ive changed my code to

 

<?php

$result = mysql_query("SELECT * FROM xbox_games order by gameid");
	while ($row = mysql_fetch_assoc($result))
	                {		    
	                $array = array();
	                $array[id] = $row['gameid'];
	                $array[title] = $row['gametitle'];
	                $array[cover] = $row['cover'];
	                }
?>

<div class="noticia"><?php echo $array[title] . "<br />";?><div>

 

This is now only echo-ing the last row of my database? Does anybody know what i am doing wrong?

[Tonic-_-]

Ive changed my code to

 

<?php

$result = mysql_query("SELECT * FROM xbox_games order by gameid");
	while ($row = mysql_fetch_assoc($result))
	                {		    
	                $array = array();
	                $array[id] = $row['gameid'];
	                $array[title] = $row['gametitle'];
	                $array[cover] = $row['cover'];
	                }
?>

<div class="noticia"><?php echo $array[title] . "<br />";?><div>

 

This is now only echo-ing the last row of my database? Does anybody know what i am doing wrong?

 

You can use either:

while ($row = mysql_fetch_assoc($result))

                {    

       

                $array[id] = $row['gameid'];

                $array[title] = $row['gametitle'];

                $array[cover] = $row['cover'];

                                echo "<div class=\"noticia\">$array['title']</div>";

 

                }

or

 

while ($row = mysql_fetch_assoc($result))

                {    

                $array[id][] = $row['gameid'];

                $array[title][] = $row['gametitle'];

                $array[cover][] = $row['cover'];

                }

foreach($array as $key=>$value)

{

echo "<div class=\"noticia\">$array['title'][$key]<div>

}

[hoponhiggo]

Sorry guys, im still not getting this...

 

I have used the above code, and started to get parse and white space errors so i removed all the html tags.

 

I now have the records from th table listed in unbroken text across my page.

 

I know this is now a html thing, but can anybody advise me on how i can display these records in a table?

 

The table should contain 2 columns (gametitle & cover), and all the rows of the table

[gizmola]

$array = array();

$array[id] = $row['gameid'];

$array[title] = $row['gametitle'];

$array[cover] = $row['cover'];

 

WRONG ... wrong wrong wrong wrong wrong wrong wrong wrong.  No. Not right, incorrect, ahem, no sir.

 

If one really wanted to do that then outside the while loop:

 

$rows = array();

 

Inside the loop:

 

$rows[] = $row;

 

 

[gizmola]

Sorry guys, im still not getting this...

 

I have used the above code, and started to get parse and white space errors so i removed all the html tags.

 

I now have the records from th table listed in unbroken text across my page.

 

I know this is now a html thing, but can anybody advise me on how i can display these records in a table?

 

The table should contain 2 columns (gametitle & cover), and all the rows of the table

 

You got some bad code from someone who doesn't know php well enough to be giving anyone advice. Unfortunately it happens here.  You don't have to load everything into an array first in this case because there is nothing complicated that you're doing with the table.  You simply need to output the table and table header outside the loop.

 

Inside while .. fetch loop, output your

....

 

Something like this:

 

echo '</pre>
<table>';
echo 'TitleCover';
$result = mysql_query("SELECT * FROM xbox_games order by gameid");
while ($row = mysql_fetch_assoc($result)) {
    echo "{$row['title']}{$row['cover']}";
}
//close out table
echo '</table>';<br>?&g

[/code]

[hoponhiggo]

Gizmola,

 

thats brilliant. Works first time. Thank you.

 

Although this leads me to my next problem...the 'covers' column is a file name for a picture in my directory, and i want to try and display the picture, not the file name.

 

To acheive this on other parts of my site for other files i have used the code:

 

<?php
//to display image from source
$dir = "profpics";

$sql = "SELECT prof_pic FROM users WHERE users.id = $userid";
$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) == 0) die("Username not found in database.");

$row = mysql_fetch_array($res);
echo "<img src='$dir/{$row['prof_pic']}' width='38' height='38'><br>";

?>

 

Is it possible to adapt this and include it in the code you provided, or would it have to be re-written completly different?

[gizmola]

That strategy should work fine... just emit the

[hoponhiggo]

would this work?

 

<?php

//to display image from source$dir = "360_covers";

echo '<table>';echo '<tr><th>Title</th><th>Cover</th></tr>';$result = mysql_query("SELECT * FROM xbox_games order by gameid");while ($row = mysql_fetch_assoc($result)) {    echo "<tr><td>{$row['title']}</td><td>$dir/{$row['cover']}</td></tr>";}//close out tableecho '</table>';

?>

[gizmola]

No, do you understand what an img tag is and how it works?

 

 

My best guess, because i don't know where the images actually reside and what the value of $dir would be.

[hoponhiggo]

Yeah, but i read...

 

That strategy should work fine... just emit the <img src=  around the $row['cover']. 

 

and pressumed this meant to drop the <img src= tag

 

Thanks you anyway. You have been a great help

[gizmola]

Haha, yeah i see the issue.  I wrote "emit" which in this context means to output.  I should just say echo or print,  but i've used emit for years.  I never thought about it that much but I can see how it could easily be confused with "omit".   

 

 

[hoponhiggo]

I cant work out how to limit the size of the images being displayed?

 

i want to use:

 

<?php

//to display image from source
$dir = "360_covers";

echo '<table>';
echo '<tr><th>Title</th><th>Cover</th><th>Test</th></tr>';
$result = mysql_query("SELECT * FROM xbox_games order by gametitle");
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr><td>{$row['gametitle']}</td><td><img src=\"$dir/{$row['cover']}\" width='38' height='38''></td><td>{$row['cover']}</td></tr>";
}
//close out table
echo '</table>';
?>

 

but this is causing errors in my syntax

[gizmola]

You have an extra single quote on height='38''.  Should be ='38'

[hoponhiggo]

Thank you . I actualy noticed that and fixed it shortly after posting but never had chance to update.

 

As you can tell, im new to PHP and HTML so i am learning all the time and find your prompts very helpfull.

 

The next thing i need to look at is how to add a form which will would allow a session user to write a review article on the any of the individual results, and update the database.

 

Is that possible?

[gizmola]

Sure.  Make a new topic please.