Results from db, then another db query with result

[davieboy]

Hi everyone
Im trying to query the database, then with on of the fields from that query, query a second table and bring results

ie:
first db result gives me IDs 2,4,7.
Then query the second db to get the indormation from there thats tables have those IDs

$result = mysql_query("SELECT * FROM eventuser WHERE user_id = '$user_id'")or die (mysql_error()) ;   

			while($r=mysql_fetch_array($result))    

		{ 

		$id = $r["id"];
		$event_code = $r["event_code"];
		$tickets= $r["tickets"];
		$notifications= $r["notifications"];

		//echo "$id|$event_code|$tickets|$notifications&nbsp;&nbsp;&nbsp;<a href=\"members.php?action=edit_my_events&info_alg_totTMR=$id\">Edit</a> | <a href=\"events.php?action=view_event&info_alg_totTMR=$id\">View</a><br>";

		foreach (array("$event_code") as $value){
		echo "TEST $value<br>"; <------ THIS LINE
		// 
		// $result = mysql_query("SELECT * FROM events WHERE session_id = '$value' ORDER BY id")or die (mysql_error()) ;   
		//while($r=mysql_fetch_array($result))    

		//{ 

		//$id = $r["id"];
		//$event_code = $r["session_id"];
		//}
		//echo "$id/$event_code";



		}
}

<-----THIS LINE
Gives me the right results, ie the fields im looking for
Im just unsure how to then get the DB to query with those in to foreach command

Any ideas at all?

Dave

[Weedpacket]

Er.... have you tried uncommenting the bit that's commented out (and then putting the line that echoes the $id and $event_code inside the loop that goes through the different values for $id and $event_code)?

When you say "db" are you sure you mean "database" and not "table"? Because I see only one database being used here.

Having a query inside a loop that's driven by the results of another query is a sign that you're Doing It Wrong. The database is fully capable of joining the results of two tables together into one query itself.

Now, it's tricky to write the query completely correctly because you've used * instead of listing the fields you actually want to retrieve.

But at a guess...

Select
	eventuser.id,
	eventuser.event_code,
	eventuser.tickets,
	eventuser.notifications,
	events.id,
	events.session_id
From
	eventuser, events
Where
	eventuser.user_id = '$user_id'
	and events.session_id = eventuser.eventcode
Order by
	eventuser.id, events.id

[davieboy]

Hi Weedpacket, longtime mate, hope you are doing well.

Thanks for the reply.
Sorry, yet one databease, two tables.

when uncommenting it only shows one result from the db.

using the code you posted, how would one echo the information and display it?

Dave

[Weedpacket]

davieboy wrote:

when uncommenting it only shows one result from the db.

Yes, like I said:

and then putting the line that echoes the $id and $event_code inside the loop that goes through the different values for $id and $event_code

using the code you posted, how would one echo the information and display it?

Ah, if you want to do that you'll need to do some programming. And a big part of programming is designing suitable data structures. Get those right and the rest of the job is usually pretty straightforward.

// You'll need to come up with sensible names for these things.
$users_events = array();
for each result $row
{
    $eventuser_id = $row['eventuser.id']; // Is that what it's called? I haven't checked.
    $eventuser_eventcode = $row['eventuser.event_code'];
    // ... and so on.

if(!isset($users_events[$eventuser_id]))
{
    $users_events[$eventuser_id] = array(
        'eventcode' => $eventuser_eventcode,
        // and other eventuser things from the $row.
        'events'=>array(),
}
$users_events[$event_user_id]['events'][] = /*an array of events things from the $row*/;
}

The rest of the job is pretty straightforward.

foreach($users_events as $eventuser_id => $eventuser)
{
    // Do things that need doing once for each eventuser, including a loop...
    foreach($eventuser['events'] as $events) // It should be "$event" but I'm being as consistent as you are about naming things
    {
        // Do things that need doing for each event.
    }
}

[davieboy]

Thanks for the reply

Right i think ive got it

mysql_connect("$db_host","$db_user","$db_pass"); //then connect as user
			mysql_select_db("$db_name"); //select which database you want to edit

		$user_id = $_SESSION['user_id'];
		$result = mysql_query("Select
eventuser.id,
eventuser.event_code,
eventuser.tickets,
eventuser.notifications,
events.id,
events.session_id
From
	eventuser, events
Where
	eventuser.user_id = '$user_id'
	and events.session_id = eventuser.event_code
Order by
	eventuser.id, events.id")or die (mysql_error());


// You'll need to come up with sensible names for these things. 
$users_events = array(); 
foreach result $row
{ 
    $eventuser_id = $row['eventuser.id']; // Is that what it's called? I haven't checked. 
    $eventuser_eventcode = $row['eventuser.event_code']; 
	$eventuser.tickets = $r["eventuser.tickets"];
	$eventuser.notifications =$r['eventuser.notifications'];
	$events.id = $r['events.id'];
	$events.session = $r['events.session_id'];
    // ... and so on. 

if(!isset($users_events[$eventuser_id])) 
{ 
    $users_events[$eventuser_id] = array( 
        'event_code' => $eventuser_event_code,
		 'tickets' => $eventuser_tickets,
		 'notifications' => $eventuser_notifications,
        // and other eventuser things from the $row. 
        'events'=>array(), 
} 
$users_events[$event_user_id]['events'][] = /*an array of events things from the $row*/; 
} 
foreach($users_events as $eventuser_id => $eventuser) 
{ 
    // Do things that need doing once for each eventuser, including a loop... 
    foreach($eventuser['events'] as $events) // It should be "$event" but I'm being as consistent as you are about naming things 
    { 
     echo "<!-- loop -->
            <li>
				<h1>$sport</h1>
				<h2>$event_name <a href=\"events.php?action=event_info&catagory=$event_name\"></a></h2>
				<h3>Where is it?</h3>
				<h4>$event_date/2011</h4>
				<ul id=\"toggle\">
					<li class=\"tickets\">";
					if($tickets > 0){
					echo "<h1>Tickets</h1>
						<p>You have $tickets tickets</p>";
						}
					else {
					echo "<h1>Tickets</h1>
						<p>You have no tickets</p>";
						}
				echo "</li>
					<li class=\"directions\">
						<h1>How to Get There</h1>
						<p>Your already here<br />
							why not check in!</p>
					</li>
				</ul>
			</li>
			<!-- /loop -->";
			} 
    } 

Im getting an error on

foreach result $row

Parse error: syntax error, unexpected T_STRING, expecting '(' in /home/------/public_html/test/index.php on line 77

Glad im finally learning about joining lol

Dave

[johanafm]

davieboy;10993013 wrote:

Im getting an error on

foreach result $row

Parse error: syntax error, unexpected T_STRING, expecting '(' in /home/------

The statement starts with foreach, which is all fine. After that comes T_STRING (result), but PHP expected a '('. Solution: insert ( before result, run script again and tackle the next parse error in the same way.

[davieboy]

cool
with (result $row
im getting unexpected T_VARIABLE
Where do/did we define that $row?

Dave

[johanafm]

Variables always starts with $, thus $row is a variable and the only variable on that row. "Unexpected variable" means that a variable wasn't expected - something else was. Thus, something else has to come before $row.

If you look at [man]foreach[/man] it should be obvious what's missing.

[davieboy]

im thiunking $users_events?

Dave

[johanafm]

Do you still think that after trying it?

If not, let's read the doc again

PHP doc entry for foreach wrote:

foreach (array_expression as $value)

While you have have (well, probably have since you havn't showed what the code looks like after you fixed the missing parenthesis problem)

foreach (result $row)

And let's compare their parts

foreach
foreach

Identical!

(
(

Identical

array_expression
result

Identical!
... assuming (for now) result is an array expression (this is most likely the next error you will get)

Next up is

as
$row)

Not identical

At this point, your statement contains nothing else, while the php doc statement still contains $result).
However, since $row) and $result) seems to be so similar, perhaps you should instead compare those two, leaving nothing in your code to be compared with "as".

as
[missing!]

and finally

$result)
$row)

They use different variable names, but can for all other intents be considered identical.

[davieboy]

sorry for delay in reply.
been at work all week

ok this is my code after the first suggestions

			include 'config.php';
			mysql_connect("$db_host","$db_user","$db_pass"); //then connect as user
			mysql_select_db("$db_name"); //select which database you want to edit

		$user_id = $_SESSION['user_id'];
		$result = mysql_query("Select
eventuser.id,
eventuser.event_code,
eventuser.tickets,
eventuser.notifications,
events.id,
events.session_id
From
	eventuser, events
Where
	eventuser.user_id = '$user_id'
	and events.session_id = eventuser.event_code
Order by
	eventuser.id, events.id")or die (mysql_error());


// You'll need to come up with sensible names for these things. 
$users_events = array(); 
foreach ($users_events
{ 
    $eventuser_id = $row['eventuser.id']; // Is that what it's called? I haven't checked. 
    $eventuser_eventcode = $row['eventuser.event_code']; 
	$eventuser.tickets = $r["eventuser.tickets"];
	$eventuser.notifications =$r['eventuser.notifications'];
	$events.id = $r['events.id'];
	$events.session = $r['events.session_id'];
    // ... and so on. 

if(!isset($users_events[$eventuser_id])) 
{ 
    $users_events[$eventuser_id] = array( 
        'event_code' => $eventuser_event_code,
		 'tickets' => $eventuser_tickets,
		 'notifications' => $eventuser_notifications,
        // and other eventuser things from the $row. 
        'events'=>array(), 
} 
$users_events[$event_user_id]['events'][] = /*an array of events things from the $row*/; 
} 
foreach($users_events as $eventuser_id => $eventuser) 
{ 
    // Do things that need doing once for each eventuser, including a loop... 
    foreach($eventuser['events'] as $events) // It should be "$event" but I'm being as consistent as you are about naming things 
    { 
     echo "my echos";
}

dave