MYSQL Question - PLEASE HELP

[magnatique]

Hey there...


I am trying to figure out the best way to do this...


right now, I do one query where I gather the info needed to build the page...


basically, it returns 4 rows, and with each mysql_fetch_row, I build a html table to display an update... (example, it would build the page listing girl1, girl2, girl3, girl4 with her info such as description, name, etc...)


NOW, I need to test each of these girls against ANOTHER table in mysql database. Unfortunately, I cannot combine the field of that table directly in the same table as the original one, since it's totally different data...

it would basically be a simple query like

SELECT update_info.girl,surfer_behavior.clicks
FROM update_info left join surfer_behavior
on update_info.girl = surfer_behavior.girl;


But, if I split the queries like that, I would have to run the last query 4 times for each girl inside the loop that builds the page, as I need to include that data in order to build the one girl's table...
(basically, I would test if surfer_behavior.clicks < 100, don;t display 'congrats')


What I am wondering is if there's any way you MYSQL gurus might know for me to Sort of JOIN these two queries together with the first one...


in other words, how do I add this to the original query : "and if votes.clicks >100 for current girl in this row, add a column named 'test' that says true"

[mrthumbs]

store the results of each query in a differect array

[magnatique]

I edited my post, basically for the second part I need to do a left join and test if it's null, or bigger than something...



---

mr thumbs, I know how I could do it doing 5 queries on the page....


I'm trying to find out if there's a way I could combine it all in one....

[swedguy]

Originally posted by magnatique
I edited my post, basically for the second part I need to do a left join and test if it's null, or bigger than something...



---

mr thumbs, I know how I could do it doing 5 queries on the page....


I'm trying to find out if there's a way I could combine it all in one....

I didn't really follow you in your first post. But if you only need to do those tests.....

SELECT ...... WHERE row IS NULL OR row > 100

[magnatique]

LOL, I'm not clear at all it seems...

is there any Mysql gurus I could reach on ICQ? that'd be WAY easier to explain what I want to do there heheh


if anyone feel helpful tonite, let me know by all means ;)

[Mortimer]

mag: what would help us in telling you what your query should be would be to post the output of "explain table" for both tables you need to get data from, tell us which data you need to get out, and under which condition. Then we'll be able to write a simple query string that will perform that

[magnatique]

ok, this isn't clear at all... I'll go and fetch some data, will be easier to explain with the real stuff instead of fictional stuff

[Lane]

Originally posted by magnatique
Hey there...


I am trying to figure out the best way to do this...


right now, I do one query where I gather the info needed to build the page...


basically, it returns 4 rows, and with each mysql_fetch_row, I build a html table to display an update... (example, it would build the page listing girl1, girl2, girl3, girl4 with her info such as description, name, etc...)


NOW, I need to test each of these girls against ANOTHER table in mysql database. Unfortunately, I cannot combine the field of that table directly in the same table as the original one, since it's totally different data...

it would basically be a simple query like

SELECT update_info.girl,surfer_behavior.clicks
FROM update_info left join surfer_behavior
on update_info.girl = surfer_behavior.girl;


But, if I split the queries like that, I would have to run the last query 4 times for each girl inside the loop that builds the page, as I need to include that data in order to build the one girl's table...
(basically, I would test if surfer_behavior.clicks < 100, don;t display 'congrats')


What I am wondering is if there's any way you MYSQL gurus might know for me to Sort of JOIN these two queries together with the first one...


in other words, how do I add this to the original query : "and if votes.clicks >100 for current girl in this row, add a column named 'test' that says true"

I dont understand the part where you say you have to run the query 4 times...


you can get all the data with a single query.. like this:

select update_info.girl, update_info.blah, update_info.huh, update_info.something, surfer_behavior.clicks .....

etc..

[JSA Matt]

I'm not to clear on your question phil... ICQ me tomorrow and i'll help you out.

What I got from your post.. I think your best bet would be to run the first query and put all the data into an array. While looping through the results of the second query you could check whatever variables you need to from the other array. Using the in_array function or something similar. Not sure if that made any sense so hit me up tomorrow.

Gonna grab a beer and watch some quality tv.

[extreme]

Hm, add another LEFT JOIN?

SELECT UI.girl as girl,SB.clicks as surferclicks,V.clicks as voteclicks FROM update_info as UI
LEFT JOIN surfer_behavior as SB on UI.girl = SB.girl
LEFT JOIN votes as V on V.girl = SB.girl;


.. something like that maybe?


edited for typo. Hm, maybe the "as" things just confused...

SELECT update_info.girl as girl,surfer_behavior.clicks as surferclicks,votes.clicks as voteclicks FROM update_info
LEFT JOIN surfer_behavior on update_info.girl = surfer_behavior.girl
LEFT JOIN votes on votes.girl = surfer_behavior.girl;

[JDog]

www.mysql.com

jDoG

[magnatique]

ok, I was totally unclear there ;)


I have put live examples in here, that should be much clearer...


re-edited the message cuz this wasn't clear at all... putting the real queries and some sample data in...


here's the main query...

SELECT update_info.code, update_info.collection, update_info.short_name, UNIX_TIMESTAMP(update_info.date_start) AS date_start, update_info.total_votes, update_info.total_score
FROM update_info
WHERE date_start < now( )
ORDER BY date_start DESC
LIMIT 2 , 2;

basically, it returns 2 rows in this example...

+---------+------------+------------+------------+-------------+-------------+
| code | collection | short_name | date_start | total_votes | total_score |
+---------+------------+------------+------------+-------------+-------------+
| anna01 | hardcore | Anna T | 1064373796 | 0 | 0 |
| jenny01 | blowjob | Jenny J | 1064286997 | 0 | 0 |
+---------+------------+------------+------------+-------------+-------------+


now, I also need to run 2 other queries to see if an ip matches...

mysql> SELECT votes.code,votes.ip_address
-> FROM votes
-> WHERE code='jenny01' AND ip_address='24.201.235.105';
+---------+----------------+
| code | ip_address |
+---------+----------------+
| jenny01 | 24.201.235.105 |
+---------+----------------+
1 row in set (0.00 sec)

AND


mysql> SELECT votes.code,votes.ip_address
-> FROM votes
-> WHERE code='anna01' AND ip_address='24.201.235.105';
Empty set (0.00 sec)




That has to be done WHILE I'm building the page from the earlier mysql_query... (as it gets the codes to check for inside that query)


is there a way I could add another column to the first query returned, testing if there is an IP Match using the code of that row?

for example, it would return something like



+---------+------------+------------+------------+-------------+-------------+---------------+
| code | collection | short_name | date_start | total_votes | total_score | Test |
+---------+------------+------------+------------+-------------+-------------+---------------+
| anna01 | hardcore | Anna T | 1064373796 | 0 | 0 |24.201.235.105 |
| jenny01 | blowjob | Jenny J | 1064286997 | 0 | 0 |NULL |
+---------+------------+------------+------------+-------------+-------------+---------------+

[extreme]

Uhm ...



... LEFT JOIN votes ON votes.code = update_info.code ... ?

[extreme]

SELECT update_info.code, update_info.collection, update_info.short_name, UNIX_TIMESTAMP(update_info.date_start) AS date_start, update_info.total_votes, update_info.total_score,votes.ip_address as Test
FROM update_info
LEFT JOIN votes ON votes.code = update_info.code
.
.
bla bla

[magnatique]

Originally posted by extreme
Uhm ...



... LEFT JOIN votes ON votes.code = update_info.code ... ?

uhm, that would work if there was only 1 row with the same 'code' cell in the votes table...

but, it's more than that...


if there's 3 surfers that would vote, then it'll have

jenny01 | 111.111.111.111
jenny01 | 125.125.125.125
jenny01 | 142.142.142.142


see what I mean?

[Penthouse_mike]

I assumed you are getting the ip to check from your program not from mysql

SELECT u.code,
u.collection,
u.short_name,
UNIX_TIMESTAMP(u.date_start) AS date_start,
u.total_votes,
u.total_score,
v.code,
v.ip_address

FROM update_info u,
votes v

WHERE u.date_start < now( )
AND u.code = v.code
AND v.ip_address = '$IP'

ORDER BY u.date_start DESC

[Mortimer]

Originally posted by extreme
Uhm ...



... LEFT JOIN votes ON votes.code = update_info.code ... ?

seems about right to me
would give the following query:

SELECT update_info.code, update_info.collection, update_info.short_name, UNIX_TIMESTAMP(update_info.date_start) AS date_start, update_info.total_votes, update_info.total_score, votes.ip_address
FROM update_info
LEFT JOIN votes ON votes.code = update_info.code
WHERE date_start < now( )
ORDER BY date_start DESC
LIMIT 2 , 2;

[Mortimer]

Originally posted by magnatique


uhm, that would work if there was only 1 row with the same 'code' cell in the votes table...

but, it's more than that...


if there's 3 surfers that would vote, then it'll have

jenny01 | 111.111.111.111
jenny01 | 125.125.125.125
jenny01 | 142.142.142.142


see what I mean?

then you need to add the following:

SELECT update_info.code, update_info.collection, update_info.short_name, UNIX_TIMESTAMP(update_info.date_start) AS date_start, update_info.total_votes, update_info.total_score, votes.ip_address
FROM update_info
LEFT JOIN votes ON votes.code = update_info.code
WHERE date_start < now( ) AND votes.ip_address = '24.201.235.105'
ORDER BY date_start DESC
LIMIT 2 , 2;

[magnatique]

Originally posted by Mortimer



seems about right to me
would give the following query:

SELECT update_info.code, update_info.collection, update_info.short_name, UNIX_TIMESTAMP(update_info.date_start) AS date_start, update_info.total_votes, update_info.total_score, votes.ip_address
FROM update_info
LEFT JOIN votes ON votes.code = update_info.code
WHERE date_start < now( )
ORDER BY date_start DESC
LIMIT 2 , 2;

yep... but the problem here is, it checks IF ANYONE Clicked.

Not IF SURFER Clicked...


see what I mean? I am surfer with Ip : 24.201.235.105... I have clicked on one of them, BUT, with that left join, I get these results...

+---------+------------+------------+------------+-------------+-------------+----------------+
| code | collection | short_name | date_start | total_votes | total_score | Test |
+---------+------------+------------+------------+-------------+-------------+----------------+
| anna01 | hardcore | Anna T | 1064373796 | 0 | 0 | NULL |
| jenny01 | blowjob | Jenny J | 1064286997 | 0 | 0 | 11.111.111.222 |
+---------+------------+------------+------------+-------------+-------------+----------------+


not my ip, cuz I was the 2nd surfer to click on that link, not the first (the left join picked the first one it saw)


I'd have to specify on the left join that the IP must be 24.201.235.105, or return null

[Mortimer]

Originally posted by magnatique



yep... but the problem here is, it checks IF ANYONE Clicked.

Not IF SURFER Clicked...


see what I mean? I am surfer with Ip : 24.201.235.105... I have clicked on one of them, BUT, with that left join, I get these results...

+---------+------------+------------+------------+-------------+-------------+----------------+
| code | collection | short_name | date_start | total_votes | total_score | Test |
+---------+------------+------------+------------+-------------+-------------+----------------+
| anna01 | hardcore | Anna T | 1064373796 | 0 | 0 | NULL |
| jenny01 | blowjob | Jenny J | 1064286997 | 0 | 0 | 11.111.111.222 |
+---------+------------+------------+------------+-------------+-------------+----------------+


not my ip, cuz I was the 2nd surfer to click on that link, not the first (the left join picked the first one it saw)


I'd have to specify on the left join that the IP must be 24.201.235.105, or return null

hmm, then even my second option might not work, since it will skip the girl if there is no entry with the IP you are looking for for that girl, instead of returning all the infos with a NULL value for ip_address...

[Mortimer]

although this might solve your problem:

SELECT update_info.code, update_info.collection, update_info.short_name, UNIX_TIMESTAMP(update_info.date_start) AS date_start, update_info.total_votes, update_info.total_score, votes.ip_address
FROM update_info
LEFT JOIN votes ON votes.code = update_info.code AND votes.ip_address = '24.201.235.105'
WHERE date_start < now( )
ORDER BY date_start DESC
LIMIT 2 , 2;


try this on your mysql and tell me if it works...

[magnatique]

Originally posted by Mortimer
although this might solve your problem:

SELECT update_info.code, update_info.collection, update_info.short_name, UNIX_TIMESTAMP(update_info.date_start) AS date_start, update_info.total_votes, update_info.total_score, votes.ip_address
FROM update_info
LEFT JOIN votes ON votes.code = update_info.code AND votes.ip_address = '24.201.235.105'
WHERE date_start < now( )
ORDER BY date_start DESC
LIMIT 2 , 2;


try this on your mysql and tell me if it works...

NICE, that's what I wanted to do, exactly ;)

+---------+------------+------------+------------+-------------+-------------+----------------+
| code | collection | short_name | date_start | total_votes | total_score | ip_address |
+---------+------------+------------+------------+-------------+-------------+----------------+
| anna01 | hardcore | Anna T | 1064373796 | 0 | 0 | NULL |
| jenny01 | blowjob | Jenny J | 1064286997 | 0 | 0 | 24.201.235.105 |
+---------+------------+------------+------------+-------------+-------------+----------------+
2 rows in set (0.00 sec)

[Mortimer]

Originally posted by magnatique



NICE, that's what I wanted to do, exactly ;)

content d'avoir aidé;)

[magnatique]

I knew there was some GURU in there ;)

[magnatique]

Originally posted by Mortimer


content d'avoir aidé;)

un quebecois en plus? hehehe ou cousin de france?

[Mortimer]

quebecois

tu viendras nous voir sur bizadulte.com