Quick probably easy question - Doing multiple query's

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Quick probably easy question - Doing multiple query's

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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)
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

volka it works like a charm. Your help is much appreciated by me!

Thanks agian

-Steve
Post Reply