Fetch from two tables without array

[Bradley99]

Hi guys,

 

I'm having some problem with a piece of code, which should be displaying 5 things from 2 different tables. Here's my code:

 

      <?

   $showevent = mysql_query("SELECT * FROM fights WHERE event='$viewpick' AND id IN (SELECT fight_id FROM betting WHERE user_id='$user->id') ORDER by id ASC");
   while($the=mysql_fetch_object($showevent)){ 
   


  echo "
	  <tr><td class='subtableheader' colspan='1'>Red Corner</td>
	  <td class='subtableheader' colspan='1'>Blue Corner</td>
	  <td class='subtableheader' colspan='1'>Event</td>
	  <td class='subtableheader' colspan='1'>My Pick</td>
	  <td class='subtableheader' colspan='1'>Winner</td></tr>
	  <tr><td class='profilerow' colspan='1'>$the-fighter1</td>
	  <td class='profilerow' colspan='1'>$the->fighter2</td>
// Above 2 are fetched from the FIGHTS table. . 
	  <td class='profilerow' colspan='1'>$viewpick</td>
//Below 2 should be fetched from BETTING table, but should coincide with the results from the FIGHTS table.
	  <td class='profilerow' colspan='1'>$fighter_id</td>
	  <td class='profilerow' colspan='1'>$winner</td></tr>


   </tr>";
}

 

Any help is appreciated.

[Barand]

A little inconsistent with your use of $the->xxx.

 

You have:

 

$the->

$the-

and some without any $the at all

[Bradley99]

That's because $the-> is used when fetching from the FIGHTS table. . . $viewpick is already determined in above code. The ones without any $the-> atall are the ones i need to be called from the BETTING table, But i Cannot figure out how to call from 2 tables in the same query, as the FIGHTS info needs to coincide with the BETTING info.

 

So - Fighter1 vs Fighter 2 & Event are determined from the fights table, i then need to fetch the pick of which that user chose in the BETTING table (fighter_id) & the winner of that fight, again from the BETTING Table (winner), The two would be aligned by the fight_id.

[Barand]

Sorry, but when people insist on using SELECT * there is no way of knowing what is selected from where.

[Bradley99]

I am selecting from FIGHTS table WHERE the event column is $viewevent (this is determined by which event is selected by user) AND id (of the fight) column is in the BETTING table WHERE the users id is $user->id. Then order it by id.

 

That sorts the first 3 columns in my code ($the-fighter1, $the-fighter2 & $viewevent). The next two i need fetching from the BETTING table.

 

So i would use. .

 

Select from BETTING table WHERE the event column is $viewevent AND the id of the fight is the same id as the corresponding result.

[Pikachu2000]

OK. Then what actually is your question? You haven't really been clear on what the problem is.

[Bradley99]

Sorry, I'm terrible at explaining my problems. My question is basically how do I get them to align, please don't tell me i just add order by fight_id at the end of the second query?

[Barand]

If you want to match data in fights table with data in betting table for the same userid then

 

SELECT ... FROM fights INNER JOIN betting ON fights.userid = betting.userid

[Bradley99]

I've managed to do it a different way now, thanks for the help.

 

Can anyone tell me if the bold part of this code is valid. . .

 

 $showevent = mysql_query("SELECT * FROM fights WHERE event='$viewevent' [b]AND id IN (SELECT fight_id FROM betting WHERE user_id='$user->id')[/b] ORDER by id ASC");
while($the=mysql_fetch_object($showevent)){  

[Bradley99]

Hi,

 

I'm trying to fetch a number of columns from two different tables in a database. I'm using the following code to fetch the first set of info. .

	   $showevent = mysql_query("SELECT * FROM fights WHERE event='$viewevent' AND id IN (SELECT fight_id FROM betting WHERE user_id='$user->id') ORDER by id ASC");
   while($the=mysql_fetch_object($showevent)){ 

I need to use this code to fetch the second set:

	   $showpick = mysql_query("SELECT * FROM betting WHERE event='$viewevent' AND user_id='$user->id'");
   while($pick=mysql_fetch_object($showpick)){ 

 

I need the results in a table like so. .

 

Red Corner - Blue Corner - Event - My Pick - Winner

$the->f1 - $the->f2  - $viewevent - $pick->fighter_id - $pick->winner

 

I need the results from the first query to MATCH the results from the second query. These can be matched by the fight_id from both tables. I just have no clue how to write the query for it.

[jcbones]

Try this: (1 query)

$sql = "SELECT f.f1,f.f2,p.fighter_id,p.winner FROM fights AS f JOIN betting AS p ON f.id = p.fight_id WHERE f.event='$viewevent' AND p.user_id='$user->id' ORDER by f.id ASC"
$showevent = mysql_query($sql) or trigger_error(mysql_error());