Extract from MYSQL into a table in two data cells

[adammc]

Hi Folks,

I am a bit stumped...
Is there any way of extracting data from MYSQL (lets say 10 items) and placing it into a table with 5 items in each column on the page?

Here is my extract code that currently displays the items in 1 big list:

#connect to database
 $cid = mysql_connect($host,$usr,$pwd);
 if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }
 mysql_select_db("$db");
 
 $SQL = " SELECT * FROM products ";
 $retid = mysql_db_query($db, $SQL, $cid);
 if (!$retid) { echo( mysql_error()); }
 else {
 echo ("<P><TABLE CELLPADDING=4 width=95% cellspacing=4>\n");
 while ($row = mysql_fetch_array($retid)) {
 $name = $row["name"];
 $price = $row["price"];
 $id = $row["id"];
 $description = $row["description"];
 $thumb = $row["thumb"];
 $pic = $row["pic"];
 echo ("<TR>");
 echo ("<TD><font class='maintext'>$name</font></TD>\n");
 echo ("<TD><font class='maintext'>$price</font></TD>\n");
echo ("<TD>$url</TD>\n");
if($pic!= "Null")
echo ("<TD><a href='$UploadAddress/$pic'><img src='$ThumbAddress/$thumb' border='0' alt='$name'></a></TD>\n");
else
echo ("<TD>No Picture</TD>\n");
 
 echo ("</TR>");
 }
 echo ("</TABLE><br><br>");
 }
?>

Any help would be greatly appreciated.

[Habtom]

Yea you can,
The code goes something similar too

global $onoff;
$onoff = 0;

if ($onoff == 0){
echo "<td>$row[0]</td>";
$onoff == 1;
}
else
{
echo "<td></td><td>$row[0]</td>";
$onoff == 1;
}

This won't give you the exact results, but you can adjust the code to fit yours.

Habtom

[alce]

I use this code when I want to display results in multiple columns:

$columns = 2; //you can change this to 3, 4, 5 etc.

$data = mysql_query ("SELECT * FROM table);
$num_rows = mysql_num_rows($data);

echo "<table>";

for($i = 0; $i < $num_rows; $i++) {
$row = mysql_fetch_array($data);
if($i % $columns == 0) {

echo "<tr>\n";
}
echo "<td>" . $row['xyz'] . "</td>\n";
if(($i % $columns) == ($columns - 1) ¦¦ ($i + 1) ==
$num_rows) {

echo "</tr>\n";
}
}
echo "</table>\n";

[adammc]

Hi Alce,

Thanks for the reply, however I cant really see how I would echo what I was echoing in my code above into the code you posted?

[adammc]

Meaning....

I can see that your row 'xyz' insdie the td cell is printing out the results, however how would I change it to add this inside the td cell :

<a href='$UploadAddress/$pic' rel='lightbox' title='$description'><img src='$ThumbAddress/$thumb' border='0' alt='$name'></a><br><font class='maintext'>$name<br>$price &nbsp;<a href='$UploadAddress/$pic' rel='lightbox' title='$description'><img src='images/view.gif' border='0' /></a></font>

[grandpa]

What you'll need to do now is refer to your variables returned from this line $row = mysql_fetch_array($data);. I am going to assume that your fields are defined in your table in the same order that they appear below. $name then is referenced as $row[0], $price as $row[1], and so on.

$name = $row["name"];
$price = $row["price"];
$id = $row["id"];
$description = $row["description"];
$thumb = $row["thumb"];
$pic = $row["pic"];

So your final result might look like this.

echo "<td><font class='maintext'>" . $row[0] . "</font></td>\n";
echo "<td><font class='maintext'>" . $row[1] . "</font></td>\n";
echo "<td>$url</td>\n";
if($row[5]!= "Null")
echo "<td><a href='$UploadAddress/$row[5]'><img src='$ThumbAddress/$row[4]' border='0' alt='$row[0]'></a></td>\n");

[adammc]

Hiya grandpa,
Thanks for your reply, I think i have grasped what you mean.

I just tried to impliment it into the script and have made a stuff up somewhere :(

Got this error:
Parse error: parse error, unexpected ')', expecting ',' or ';' in ###### on line 141

Line 141 is this line "echo "<td><a href='$UploadAddress/$row[5]'><img ......"

$columns = 2; //you can change this to 3, 4, 5 etc. 
$name = $row["name"]; 
$price = $row["price"]; 
$id = $row["id"]; 
$description = $row["description"]; 
$thumb = $row["thumb"]; 
$pic = $row["pic"]; 
$data = mysql_query ("SELECT * FROM products"); 
$num_rows = mysql_num_rows($data); 
echo "<table>"; 
for($i = 0; $i < $num_rows; $i++) { 
$row = mysql_fetch_array($data); 
if($i % $columns == 0) { 
echo "<tr>\n"; 
} 
echo "<td><font class='maintext'>" . $row[0] . "</font></td>\n"; 
echo "<td><font class='maintext'>" . $row[1] . "</font></td>\n"; 
echo "<td>$url</td>\n"; 
if($row[5]!= "Null") 
echo "<td><a href='$UploadAddress/$row[5]'><img src='$ThumbAddress/$row[4]' border='0' alt='$row[0]'></a></td>\n"); 
if(($i % $columns) == ($columns - 1) ¦¦ ($i + 1) == 
$num_rows) { 
echo "</tr>\n";

[alce]

You just need to remove the ")" from this line

echo "<td><a href='$UploadAddress/$row[5]'><img src='$ThumbAddress/$row[4]' border='0' alt='$row[0]'></a></td>\n");

it should be:

echo "<td><a href='$UploadAddress/$row[5]'><img src='$ThumbAddress/$row[4]' border='0' alt='$row[0]'></a></td>\n";

also dont forget to close the if statment, the loop and the table:

if(($i % $columns) == ($columns - 1) ¦¦ ($i + 1) ==
$num_rows) {

echo "</tr>\n";
}}
echo "</table>\n";
?>

I hope this works as you expect.

[adammc]

Alce,
thanks for picking that up, I completeely missed it :)

Ok it found another error:
Parse error: parse error, unexpected T_STRING on line 144

Line 144:

if(($i % $columns) == ($columns - 1) ¦¦ ($i + 1) ==
$num_rows) {

Sorry, but any help would be GREATLY appreicated. I ahev only been delving into PHP for about 3 months now and havent quite got the knack yet of bug checking.

[grandpa]

¦¦

Are you using the solid vertical bars? WebmasterWorld breaks them to display as ¦¦ so be sure to replace them if you have copied any code from here.

[adammc]

Grandpa... You did it, thank you so much, it works a treat!

Alce, thank you very much fro helping me with that, much appreciated.

[adammc]

One last question....

If I decided to page the results (pagination) later on the the future, would it be hard to incorporate into that code or would I be better starting from scratch?

[grandpa]

Pagination shouldn't be any more difficult to add to your code than anything else. The trick is in knowing how.

You might want to research the subject the bit, and maybe practice with some code to gain a better understanding of the process. Or, you could do it like I did, and decide that your code needs pagination, and brute force your way thru it all. :) Either way works. Best wishes.

[adammc]

Grandpa,
Thanks for the tip ;)