Page 1 of 1

Quick probably easy question - Doing multiple query's

Posted: Thu Sep 28, 2006 5:43 pm
by waradmin
First query is

Code: Select all

$result = mysql_query("SELECT * FROM mini_info WHERE sex='$_GET[term]'") or die(mysql_error());
$row = mysql_fetch_array( $result );
That will take term=_____ from the address bar and find all values in mini_profile that match.

Then I need to get the global_id associated with the matching row

Code: Select all

$row['global_id'];
Next I need to query the table called loginphp and match up that $row['global_id'] with the matching global_id in that table, then echo the fields Fname and Lname.

so far I have:

Code: Select all

<?php
	  $section = $_GET['section'];
	  if($section == "sex")
	  {
	  	$result = mysql_query("SELECT * FROM mini_info WHERE sex='$_GET[term]'") or die(mysql_error());
		?>
        <tr>
          <td valign="top">
		  <?php
		  $row_count = mysql_num_rows( $result );
		  $i == 0;
		  while($i < $row_count)
		  {
		  	$row = mysql_fetch_array( $result );
                        //SOME CODE NEEDS TO GO HERE!
			$i++;
		  } ?>
		  </td>
        </tr>
      </table>
	  <?php
	  }
	  ?>
How do I modify that to echo Fname Lname from the loginphp where the global_id matches the initial query of mini_info and grabs the global_id that matches the search term (either Male or Female)?

And it needs to loop through all matching rows, not just one of them, that is key

Thanks in advance

Posted: Thu Sep 28, 2006 6:25 pm
by volka
You don't need multiple querries for this; a JOIN will suffice.
see e.g. http://www.w3schools.com/sql/sql_join.asp
and http://en.wikipedia.org/wiki/SQL_injection

Posted: Thu Sep 28, 2006 6:41 pm
by waradmin
My query is:

Code: Select all

$result = mysql_query("SELECT mini_info.sex, mini_info.global_id, loginphp.Fname, loginphp.Lname, loginphp.global_id FROM mini_info, loginphp WHERE mini_info.sex='$_GET[term]'") or die(mysql_error());
But now when I echo $row['global_id']; it first echo's each one twice, and I am not quite sure which JOIN to use, I tried INNER JOIN and it did the same thing.

Im just trying to get it to echo the global_id that has the value $_GET[term] in its mini_info table.sex row

Posted: Thu Sep 28, 2006 7:02 pm
by volka
waradmin wrote:Im just trying to get it to echo the global_id that has the value $_GET[term] in its mini_info table.sex row
I thought, you wanted the info loginphp matching the match global_id field?
try

Code: Select all

$query = "SELECT
			mini_info.sex, mini_info.global_id, loginphp.Fname, loginphp.Lname, loginphp.global_id
		FROM
			mini_info
		JOIN
			loginphp
		ON
			mini_info.global_id=loginphp.global_id
		WHERE
			mini_info.sex='". mysql_real_escape_string($_GET[term]) . "'";
$result = mysql_query($query) or die(mysql_error());
If this doesn't work, please provide sample data (most appreciated if provided as valid sql code)

Posted: Thu Sep 28, 2006 7:09 pm
by waradmin
volka it works like a charm. Your help is much appreciated by me!

Thanks agian

-Steve