Same query on 2 tables

[effennel]

Is there a simpler/more compact way to code this?

The query is the same on the 2 tables:

$sql = "SELECT id FROM users_intake WHERE users_id=5 AND timeStamp LIKE '2005%'";
$result = mysql_query($sql);
$inside1 = mysql_fetch_object($result);

$sql = "SELECT id FROM users_state WHERE users_id=5 AND timeStamp LIKE '2005%'";
$result = mysql_query($sql);
$inside2 = mysql_fetch_object($result);

if (!$inside1 and !$inside2) {
    echo "no";
} else {
    echo "yes";
}

I use MySQL 3.23.58, so UNION is not possible. I found this alternative, but it doesn't look much better to me:

$sql = "CREATE TEMPORARY TABLE temp_union TYPE=HEAP SELECT id FROM users_intake WHERE users_id=5 AND timeStamp LIKE '2005%'";
mysql_query($sql) or die (mysql_error());

$sql = "INSERT INTO temp_union SELECT id FROM users_state WHERE users_id=5 AND timeStamp LIKE '2005%'";
mysql_query($sql) or die (mysql_error());

$sql = "SELECT id FROM temp_union";
$result = mysql_query($sql) or die (mysql_error());

$sql = "DROP TABLE temp_union";
mysql_query($sql) or die (mysql_error());

$inside = mysql_fetch_object($result);

if (!$inside) { 
    echo "no";
} else {
    echo "yes";
}

Any suggesions?

Thanks
FnL

 

[TonyGroves]

That looks like the best you're going to get!

 

[effennel]

I'm new to PHP/MySQL...

Any ways I can check out the performance associated to the 2 solutions above?

Thanks
FnL

 

[r937]

are you trying to find out whether user_id 5 has at least one row in both tables?

because you can do that with a simple join

what are you trying to do?

r937.com | rudy.ca

 

[effennel]

I'm trying to find out whether user_id 5 has
- at least one row with a string the in field timeStamp starting by 2005.
- in at least one of the tables

I tried a join before, but didn't get what I was looking for. Perhaps I had the wrong syntax. If I remember well, it was something like:

SELECT users_intake.id 
FROM users_intake, users_state 
WHERE (users_intake.login=5 AND users_intake.timeStamp='2005%')
OR (users_state.login=5 AND users_state.timeStamp='2005%')

Thanks
FnL

 

[r937]

okay, if it's one or the other, a simple join won't do it, but a cross join might

SELECT count(*) as yes
FROM users_intake
, users_state
WHERE (users_intake.login=5
AND users_intake.timeStamp like '2005%')
OR (users_state.login=5
AND users_state.timeStamp like '2005%')

this is not very efficient, but it will either return a number (which may very well me meaningless -- remember, it's a cross join) or else no results at all

r937.com | rudy.ca

 

[effennel]

I get it.

Is fair to say that my two first blocks above would be more performant (although less elegant...)?

Which would allow the best performance?
I can I evaluate/test this?

Thanks
FnL