double query..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

double query..

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

looks like that could be done with a join...
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post by ol4pr0 »

Example, never messed to much with joins might be smart tho to learn more about them.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post 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 ?!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I think you need to post some actual code or the table structures or both.
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

right.. that's what I was suggesting.. in many more words.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

feyd wrote:right.. that's what I was suggesting.. in many more words.
Ahh... sorry, my bad :oops:. Haven't understood that.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's all good. :)
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Post Reply