mad loops

Anon

Hi there everyone --

I have written a bit of code that returns the values I requested ... the problem is it
seems to loop as many times as there is entries! I believe the SQL syntax is correct and am besides myself at the moment. any help would be greatly appreciated.

here's the code!

$sql = "SELECT customer_info.name, customer_info.email, customer_info.customer_id,
requests.color_from,requests.color_to,requests.carat_from,
requests.carat_to,requests.clarity_from,requests.clarity_to,
requests.shape,requests.message
FROM
customer_info, requests";

$sql_result = mysql_query($sql,$conn)
or die("Couldn't execute query.");

// begin results formatting

echo "<TABLE BORDER=1 width='600'>";
echo "<TR>
<TH>name</TH>
<TH>email</TH>
<TH>customer_id</TH>
<TH>Color_from</TH>
<TH>Color_to</TH>
<TH>Carat_from</TH>
<TH>Carat_to</TH>
<TH>Clarity_from</TH>
<TH>Clarity_to</TH>
<TH>Shape</TH>
</TR> ";

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

//assign variables to a row array.

   $customer_id = $row["customer_id"];
   $name = $row["name"];
   $email = $row["email"];   		    
   $Color_from =     $row["color_from"];
   $Color_to =       $row["color_to"];
   $Carat_from =     $row["carat_from"];
   $Carat_to   =     $row["carat_to"];
   $Clarity_from =   $row["clarity_from"];
   $Clarity_to =     $row["clarity_to"];
   $Shape  =         $row["shape"];
   $message =        $row["message"];	

 echo "<TR>
         <TD>$name</TD>
         <TD>$email</TD>
         <TD>$customer_id</TD>
		 <TD>$Color_from</TD>
		 <TD>$Color_to</TD>
		 <TD>$Carat_from</TD>
		 <TD>$Carat_to</TD>
		 <TD>$Clarity_from</TD>
		 <TD>$Clarity_to</TD>
		 <TD>$Shape</TD>		
		 </TR>";

          }

echo "</TABLE>";

</SCRIPT>

thanks again
Chris

Anon

Your SQL statement fetches all date from the tables because there is no WHERE clause to limit the number of results.

Next, you put in:

while ($row = mysql_fetch_array($sql_result))

which means: do while there are still results in the $sql_result variable

thus, it prints all results from the query, which is all the data from your tables.

Anon

Vincent thanks for your reply,

The query does indeed return all the results from data residing in
MySQL, the problem is that it returns it x5 (if there is 5 entries, I used the the auto_increment function to generate unique #'s ).
am I better off inplementing a for loop and counting the array?

thanks in advance,
Chris

Anon

Chris, I\'m a little puzzled by what it is you want to do?

You tell your program to fetch all data in your table,
and your WHILE loop prints all the data in the result.

If you have 5 entries in your table, all 5 will be printed because that is what you are asking for.

If you want to return just one particular result, you have to change your sql statement to include a WHERE clause to select only those rows in your table that you want.

SELECT * FROM table WHERE id=1;

SELECT * from table WHERE username LIKE \"%bert%\";

Anon

Well what was happening for some -reason?
was that it was returning all the data form
the tables but it was returning it 25 times
instead of 5. (mad loop?)
I decided to create a new table and create
the columns that previously spanned multiple
tables in this new table..
now I get the results I was looking for without the prior complication and a much simpler SELECT statement without joins.
It seems to be correct now, sorry If I did not clarify the issue properly, I thank you for your concern.

Cheers,
Chris

Anon

Arg, sorry, I missed the JOIN part of your sql statement.

If you do a straight join like you did, it selects all data from all the first table, for each entry in the second.
So if you have 5 rows in the first table and 5 in the second, it selects all 5 rows of the second table for each of the 5 rows in the first table.
Thats 5x5=25 rows.

If you want to select rows from two tables that belong together, ie: they have the same ID, you might want to put in a statement like WHERE table_a.ID=table_b.ID
That way you will only get the rows from the first table that have a row with matching ID in the second table.