Page 1 of 2

double query..

Posted: Tue Jun 22, 2004 8:18 pm
by ol4pr0
dubbel query.. seems not to work.. must be a way tho.

this is what i do, or trying to accomplish

Code: Select all

$query = mysql_query('SELECT * FROM vacatures WHERE login="ik" AND active="1"');
		

while($data = mysql_fetch_assoc($query)) {
#getting rows wich i need
}
mysql_free_result($query);
mysql_close();

#sometable i whish to replace with a $row['var']  returned from the first query is that possible?  how ???
$query2 = mysql_query('SELECT * FROM sometable. WHERE login="'.$_COOKIE['USERNAME'].'" AND active="1"');
		

while($data = mysql_fetch_assoc($query2)) {
#getting all rows i need again.
}

# now the first query works.. 

# second query doesnt return a error.. neither does it return anything in #the $row['var'] i need

Posted: Tue Jun 22, 2004 8:33 pm
by RobertGonzalez
You need to run your second query in the while loop of your first query:

Code: Select all

<?php
$sql = "SELECT * FROM vacatures WHERE login='ik' AND active=1";
$query = mysql_query($sql); 
while($data = mysql_fetch_assoc($query)) { 
    //INSERT YOUR SECOND QUERY HERE...
    $sql2 = "SELECT * FROM " . $data['table_name'] . " WHERE login="'.$_COOKIE['USERNAME'].'" AND active=1";
    $query2 = mysql_query($sql2);
    // INSERT THE REST OF YOUR SCRIPT WORK HERE...
} 
mysql_close(); 
?>
I am sure there are some conditions that you'll want to add to it, but you get the gist of it, right?

Good luck.

Posted: Tue Jun 22, 2004 10:02 pm
by feyd
looks like that could be done with a join...

Posted: Thu Jun 24, 2004 4:46 am
by ol4pr0
Example, never messed to much with joins might be smart tho to learn more about them.

Posted: Thu Jun 24, 2004 4:54 am
by feyd
what data from the first table is used to access the second table? In your example the only thing similar is the field 'active'.. I think I may need to table structures and info on the relationship(s) they share to help write a join for this.

Posted: Thu Jun 24, 2004 9:42 pm
by ol4pr0
no there are more things simular ( unique id, and 2 other fields.. )

Lets say that only user / password and unique id are most secure to check if that information belongs to the same person.

while logging in i am updating a session field with the current session which i use for further reference on the first table.

But there is one field in the users table that i need to know what other table i should access in order to show the information from that second table.

so example

user | table 1 | table 2 | table 3 |
refer-> user 1 user 2 user 3

hopes that makes some sence

When making a join, doesnt that conflict when echo $rows ? meaning when some fields from table1 have the same var as table2 but they need to be echo.d back ona differant location somewhere else on that same page ?!

Posted: Thu Jun 24, 2004 9:53 pm
by feyd
I think you need to post some actual code or the table structures or both.

Posted: Thu Jun 24, 2004 10:28 pm
by ol4pr0
oke..

Code: Select all

# something i am using now ..more r less.
# i updated the session id like a sec ago, so if the user is logged in i imagen its the best way to do an
# extra check on if i get the right information returned.

$result = mysql_query('SELECT * FROM users WHERE login="'.$_COOKIE['USERNAME'].'" AND active="1" AND session="'.$_COOKIE['PHPSESSID'].'"');

		while($row = mysql_fetch_assoc($result)) {

		if ($row['cuenta'] == 'mp3') {

#now i am using the unique_id i got from my first query + the extra 'cuenta'  to determen what information i should show
# becuase some people might just have registerd to see the latest mp3 or movies or games.
# so i have field names  mp3 , movies , games  this is only in users table availeble.
#
# so if user has mp3 field in his table he will see totally differant information than the other ones ofcourse
# so my next is the check that table with the unique_id ( which is randomly generated so i guess its save enough. 

# now the unique id howcome is that in both tables..
# every user can post a mp3 , movie, game, story or download or whatever, when he does that he does that from inside the admin panel so when he / she clicks on a link the unique_id is automaticly send to that form in a hidden field. when submitted that id will be put in table as well to gether with his user name, date, email. and some others..

#so ones posted a message or whatever there are more fields to check with. | user | password | email | unique_id ...
#which i use again if he / she wants to delete modify or deactivate the post.


$cur_result = mysql_query('SELECT * FROM '.$row['cuenta'].' WHERE login="'.$_COOKIE['USERNAME'].'" AND                  
unique_id="'.$row['unique_id'].'"');

while($cur = mysql_fetch_assoc($cur_result)) {
# and do the echo ing of the fields i want to display..
}

if ($row['cuenta']=='movies') {
#i start over again with another query and do the same thing
}
HOpes that makes the whole story somewhat clearer

Posted: Thu Jun 24, 2004 11:23 pm
by feyd

Code: Select all

"SELECT * FROM `users` u LEFT JOIN `mp3` a ON u.`cuenta` = 'mp3' AND u.`unique_id` = a.`unique_id` AND a.`login` = '{$_COOKIE['USERNAME']}'
LEFT JOIN `movies` b ON u.`cuenta` = 'movies' AND u.`unique_id` = b.`unique_id` AND b.`login` = '{$COOKIE['USERNAME']}'
LEFT JOIN `games` c ON  u.`cuenta` = 'games' AND u.`unique_id` = c.`unique_id` AND c.`login` = '{$COOKIE['USERNAME']}'"
the problem will be: you'll likely have to access the results with array offsets versus associative because you have duplicate names in your tables. If you have differing names for all fields, you could use associative arrays. If the rows that have the same names are indeed the same, you can probably get away with using associative arrays.

Posted: Thu Jun 24, 2004 11:44 pm
by Weirdan
feyd wrote:the problem will be: you'll likely have to access the results with array offsets versus associative because you have duplicate names in your tables. If you have differing names for all fields, you could use associative arrays. If the rows that have the same names are indeed the same, you can probably get away with using associative arrays.
The proper solution would be to name explicitly all required fields in SELECT query, aliasing them as necessary. This way you can still use associative arrays.

Posted: Thu Jun 24, 2004 11:49 pm
by feyd
right.. that's what I was suggesting.. in many more words.

Posted: Fri Jun 25, 2004 12:10 am
by Weirdan
feyd wrote:right.. that's what I was suggesting.. in many more words.
Ahh... sorry, my bad :oops:. Haven't understood that.

Posted: Fri Jun 25, 2004 1:03 am
by feyd
it's all good. :)

Posted: Fri Jun 25, 2004 2:25 am
by ol4pr0
Oke well i didnt completely understood what that all did, however i do wonder how would i get the result set out of a query like that ?

i also saw that you did not make use of the SESSID that i use as a extra, is it not wurthy of doing so, or.. cuase i feel its kinda dangerous by just using a unique_id together with some COOKIE as a reference to get the resultset.

Posted: Fri Jun 25, 2004 1:43 pm
by McGruff