I have been writing a members script to display members by region, name or business type. I have it all working, except I can't seem to get it to display the result properly. If there's one result returned, I'd like it to create the table, with the data in the first cell, and nothing but a non-breaking space in the second (currently, it only creates one TD, each result in a separate table). If there's more than one result, write to the second TD, and more than that, create a new row and do it all the same way as the first until no more rows are returned from the DB.
Can anyone shed a bit of light on this? Here's the code:
<center><?php
require "admin/db.php";


$TableName = "members";
$Link = mysql_connect ($Host, $User, $Pass);
if($last_name == "" && $category == "" && $county == "" && $id == "") {
echo "You must type in a search query.";
} else {

if($last_name) {
$Query = "SELECT * from $TableName WHERE last_name LIKE \"%$last_name%\"";
} elseif($category) {
$Query = "SELECT * from $TableName WHERE category LIKE \"%$category%\"";
} elseif($county) {
$Query = "SELECT * from $TableName WHERE county LIKE \"%$county%\"";
} elseif($id) {
$Query = "SELECT * from $TableName WHERE id='$id'";
}

// From here is where the issue starts

$num = 0;
$Result = mysql_db_query ($DBName, $Query, $Link);
while ($Row = mysql_fetch_array($Result)) {
$num++;
?>

<table width="50%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td><strong>
<?
if($Row[website]) {
echo "<a href=\"$Row[website]\">$Row[organization]</a>";
} else {
echo "$Row[organization]";
}
?>
<br>
</strong>
<?=$Row[first_name]?> <?=$Row[last_name]?><br>
<?=$Row[address]?><br>
<?=$Row[city]?>, <?=$Row[state]?><br>
<?=$Row[zipcode]?><br>
<?=$Row[phone]?><br>
<?=$Row[products]?></td>
</tr>
</table><br><br>
<?
}
if($num == 0) {
echo "No results were found for your search.";
}
}
?>

Hmm...maybe if I were to offer payment for this? How's $20 via Paypal to the first person to offer a viable, complete fix/solution. Must work as outlined above, and format the organization name in bold and linked, as shown.
Any takers?

You need to put your table in a while loop.

Something like this:



$your_sql_query;

}


echo ('What you don't want to loop');

while ( $row = mysql_fetch_array($result) )
{


echo('information you want to loop');
}
echo ('');



if you want me to put your code in this way yell ..... i'll do it for free.

Yes, I've asked for help since I can't work it out myself. I'd love to get this done...and would still pay for a viable solution. It's in a while loop currently, but it won't create the extra TDs or rows as needed. Just keeps making new tables...

<center><?php
require "admin/db.php";


$TableName = "members";
$Link = mysql_connect ($Host, $User, $Pass);
if($last_name == "" && $category == "" && $county == "" && $id == "") {
echo "You must type in a search query.";
} else {

if($last_name) {
$Query = "SELECT * from $TableName WHERE last_name LIKE \"%$last_name%\"";
} elseif($category) {
$Query = "SELECT * from $TableName WHERE category LIKE \"%$category%\"";
} elseif($county) {
$Query = "SELECT * from $TableName WHERE county LIKE \"%$county%\"";
} elseif($id) {
$Query = "SELECT * from $TableName WHERE id='$id'";
}


$num = 0;
$Result = mysql_db_query ($DBName, $Query, $Link);
while ($Row = mysql_fetch_array($Result)) {
$num++;


echo "<table width=\"50%\" border=\"0\" cellspacing=\"0\" cellpadding=\"4\">
<tr>
<td><strong>

while ( $row = mysql_fetch_array($result) )
{

if($Row[website]) {
echo "<a href=\"$Row[website]\">$Row[organization]</a>";
} else {
echo "$Row[organization]";
}

<br>
</strong>
<?=$Row[first_name]?> <?=$Row[last_name]?><br>
<?=$Row[address]?><br>
<?=$Row[city]?>, <?=$Row[state]?><br>
<?=$Row[zipcode]?><br>
<?=$Row[phone]?><br>
<?=$Row[products]?><br><br>

}
if($num == 0) {
echo "No results were found for your search.";
}
}
?></td>
</tr>
</table>


Something like that should work.

"unexpected T_STRING, expecting ',' or ';' in /display.php on line 36"

That's this line:
echo "<a href=\"$Row[website]\">$Row[organization]</a>";

Been fooling with this, and it's quite broken. After fixing the bits that had syntax errors, it once again displays in one large table...dang.

Is this the kind of output scheme you are after?

open table
while ($row = mysql_fetch_array($result)) {
open tr
open td
put in info
close td
if ($row2 = mysql_fetch_array($result)) {
open td
put in info
close td
}
else {
open td
put in non-breaking space or whatever you want
close td
}
close tr
}
close table

Yup, that's what I need. The content requires a bit of formatting, but that's the gist of it. Can you write that?

[edit]It also needs to notice if there's a need for a second (third, fourth...) row, done in the same manner.

Oooh boy... that's some ugly bit of code.. breaks atleast 50 different standards. PROPER formatting never killed anyone. Anyways, here's something that might actually work. Also, try to refrain from using double quotes unless you have to. Double quoted strings are being parsed, while single quoted strings are taken "as is".


<?php
require('admin/db.php');

$TableName = 'members';
if (!($Link = @mysql_connect ($Host, $User, $Pass)))
die ('Failed to create database connection');

if ( empty($last_name) && empty($category) && empty($county) && empty($id) ) {
echo 'You must type in a search query.';
} else {

$Query = 'SELECT * from ' . $TableName . ' WHERE ';

if (!empty($last_name)) {
$Query .= 'last_name LIKE "%' . $last_name . '%"';
} else if (!empty($category)) {
$Query .= 'category LIKE "%' . $category . '%"';
} else if (!empty($county)) {
$Query .= 'county LIKE "%' . $county . '%"';
} else if (!empty($id)) {
$Query .= 'WHERE id="' . $id . '"';
}

if(!($Result = @mysql_db_query ($DBName, $Query, $Link)))
die (mysql_errno() . ': ' . mysql_error());

if(mysql_num_rows($Result) == 0) {
echo 'No results were found for your search.';
}else{

echo '<table width="50%" border="0" cellspacing="0" cellpadding="4">' . "\n";

while ( ( $Row = mysql_fetch_assoc($Result) ) ) {
echo ' <tr>' . "\n" . ' <td><strong>' . "\n";

if( $Row['website'] ) {
echo '<a href="' . $Row['website'] . '">' . $Row['organization'] . '</a><br/>';
} else {
echo $Row['organization'] . '<br/>';
}

echo <<< EOF
</strong>
{$Row['first_name']} {$Row['last_name']}<br/>
{$Row['address']}<br/>
{$Row['city']}, {$Row['state']}<br/>
{$Row['zipcode']}<br/>
{$Row['phone']}<br/>
{$Row['products']}
</td>
</tr>
EOF;
}

echo '</table>';
}
}
?>

Also there's a bug in your initial query which prevents anyone from searching on multiple fields at once (eg. entered both a country and last name). Replacing the bunch of else ifs with this would work.


$_and = '';

if (!empty($id)) {
$Query .= 'WHERE id="' . $id . '"';
}else{
if (!empty($last_name)) {
$Query .= 'last_name LIKE "%' . $last_name . '%"';
$_and = ' AND ';
}

if (!empty($category)) {
$Query .= $_and . 'category LIKE "%' . $category . '%"';
$_and = ' AND ';
}

if (!empty($county))
$Query .= $_and . 'county LIKE "%' . $county . '%"';
}

Ugly indeed...I'm new at PHP, so haven't a clue about formatting (obviously). Thought I'd get it working first. ;)

This code produces a single <TR><TD> for each row returned. I need this to use a second TD, and if there's more rows returned, start a new <TR><TD> etc.
<tr><td>first result</td><td>second result</td> //if more
</tr><tr><td>next</td><td> another</td> //still more?
</tr><tr><td>third set</td><td>last one></td> // all done?
</tr></table>
That possible?

Oh you mean display things horizontally? That bit of code did things vertically. Welcome to PHP :)

Replace
while ( ( $Row = mysql_fetch_assoc($Result) ) ) {
echo ' <tr>' . "\n" . ' <td><strong>' . "\n";

if( $Row['website'] ) {
echo '<a href="' . $Row['website'] . '">' . $Row['organization'] . '</a><br/>';
} else {
echo $Row['organization'] . '<br/>';
}

echo <<< EOF
</strong>
{$Row['first_name']} {$Row['last_name']}<br/>
{$Row['address']}<br/>
{$Row['city']}, {$Row['state']}<br/>
{$Row['zipcode']}<br/>
{$Row['phone']}<br/>
{$Row['products']}
</td>
</tr>
EOF;
}
echo '</table>';


With


echo ' <tr>' . "\n"

while ( ( $Row = mysql_fetch_assoc($Result) ) ) {
echo ' <td><strong>' . "\n";

if( $Row['website'] ) {
echo '<a href="' . $Row['website'] . '">' . $Row['organization'] . '</a><br/>';
} else {
echo $Row['organization'] . '<br/>';
}

echo <<< EOF
</strong>
{$Row['first_name']} {$Row['last_name']}<br/>
{$Row['address']}<br/>
{$Row['city']}, {$Row['state']}<br/>
{$Row['zipcode']}<br/>
{$Row['phone']}<br/>
{$Row['products']}
</td>
EOF;

}

echo ' </tr>' . "\n" . '</table>';

Welcome indeed. ;)
I spent most of today wrestling with some code provided by another helpful soul from another board. He set me on the right path, and after several hours of chasing a misplaced " I have my solution. I quite literally finished working on this minutes ago, and was coming here to say so.

I do appreciate your assistance, and if you feel you've spent a lot of time creating this bit, I can send you a little something via Paypal. I'd hate to have you work on it and then have you think I've stiffed you.

To top everything off, in the throes of adapting this code, I think I've actually begun to see a glimmer of understanding on how it works. I love that feeling when something finally "clicks"...:D

That's up to you. Donations are always welcome :) My paypal is xtremeq_g at hotmail dot com which is also my email address. Feel free to drop an email if you need further help.

Correct me if I'm wrong, but I don't see in that code where it actually creates only 2 TDs then starts a new row? If I'm reading that right, it makes a TD for every row returned...all in the same TR?
The code I got from elsewhere has additional testing that looks for more than 2 records, and formats them into additional TRs and TDs as required...not one long TR.

Or do I have this wrong? Not asking for a rewrite, (I already have this working now), just clarification.

Lol.. so you want 2 columns with data spanning x rows? Ok can be done too. The first 2 methods print a vertical and horizontal list respectively. There are many many ways of doing multiple columns.

A simple way would be to use mysql_num_rows() to see if it's even or odd (required to know for the ending) then use $row1 = mysql_fetch_assoc() and $row2 = mysql_fetch_assoc() to load the data for column 1 and column 2, then finally loop through the amount of rows / 2 which you obtained from the previous mysql_num_rows() call. If the number of rows is an odd number you can have a final mysql_fetch_assoc() which creates a single column, then add an extra empty <td></td> to the end for html formatting (or just use colspan="2" as attribute). Pseudo code would be:

row_count = mysql_fetch_row()
while i < row_count - 1 {
mysql_fetch_assoc() first row
mysql_fetch_assoc() second row
open tr, td
print first row
close td, open td
print second row
close td, tr
i + 2
}

if i == odd number {
mysql_fetch_assoc() last row
open tr, td
print last row
close td, open td, close td, tr
}

The second method is a bit more technical but also more reliable in terms of when possibly the database connection drops during the loop (rarely happends but it would spit out a bunch of errors saying invalid mysql resource for you mysql_fetch_assoc() call). It relies on using a seperate counter line : $i = ($i + 1) % 2; which basically goes 0 1 0 1 0 1 0 1 all the time. You can then test using a simple if() to when it's 0 create a row and print the first column, when it's 1 print the second column and close the row. In the end you again test whether the number of columns is odd (test $i and see if it's 1) and insert a blank cell and close the row to make sure the html is ok.

Pseudo code:

while mysql_fetch_assoc() row {
if i == 0 {
open tr, td
print row
close td
}
if i == 1 {
open td
print row
close td, tr
}

i = (i + 1) % 2
}

if i == 1 close td, tr

Nicest thing about the second method is that you can quite easily change the number of columns from 2 to 3, 4 and pretty much anything else by changing the value after modulus (%).

heh...yup, I'm a picky bugger. I like the two approaches very much, and I believe I "get" it. Thanks very much for the help...you've earned a donation, in my opinion. :D

Nice when someone takes the time to go over tricky bits like this.

Thanks alot :) Glad to be of help