PHP MySQL Selection Question

[webmasterjunkie]

Hello All,

I am trying to check my MySQL tables, which all have a common field of user_id. I have searched on the net and tried tons of diffrent things.

Unfortunately I am stuck. I will post some of the core code of what I have below. If anyone has a better way of doing this, please let me know. I am not too familiar with JOIN or such.

<?php
	$db_connection = @mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
	@mysql_select_db($db_name, $db_connection) or die (mysql_error());
	$sql = "SELECT * FROM $course_table, $event_table, 
	$job_table, $housing_table, $news_table, $profiles_table, $products_table, $research_table, 
	$reviews_table, $services_table, $travel_table, $vehicles_table, $wanted_table 
	WHERE $course_table.user_id = '$user_id' OR $event_table.user_id = '$user_id' 
	OR $job_table.user_id = '$user_id' OR $housing_table.user_id = '$user_id' 
	OR $news_table.user_id = '$user_id' OR $profiles_table.user_id = '$user_id'	
	OR $products_table.user_id = '$user_id' OR $research_table.user_id = '$user_id' 
	OR $reviews_table.user_id = '$user_id' OR $services_table.user_id = '$user_id' 
	OR $travel_table.user_id = '$user_id' OR $vehicles_table.user_id = '$user_id' 
	OR $wanted_table.user_id = '$user_id'";
	$result = @mysql_query($sql, $db_connection) or die(mysql_error());
	$total_items = mysql_num_rows($result);
	mysql_close($db_connection);	
?>

[bodgekaloopie]

What is the error or failure you are experiencing?
You're checking your tables specifically for what?
Are you echoing results once the query is performed?

If there is more code in your document, please post all of it or we won't be able to fully grasp what it is you are trying to achieve so that we can help you.

[DrTandem1]

Yes, without knowing what you are trying to do with your script it's only a guess. If you are trying to troubleshoot this, I would add echo statements so you can see what is happening.

[Tjaart]

It looks like you are trying to 'merge' the results from multiple queries into one result set, i.e.
select * from $course_table where $course_table.user_id = '$user_id'
UNION
select * from $event_table where $event_table.user_id = '$user_id'
etc

If this is correct then what you need is a UNION...

There are a few things to be aware of, but you can find what you need at http://dev.mysql.com/doc/refman/5.0/en/union.html

Let us know if that's not what you are trying to achieve.

Good luck

[Tjaart]

Using a UNION result set in a webpage could be a mission, especially if you have that many tables, and you might be better of just doing individual queries to the server or maybe doing the JOIN if you want the information returned for many '$user_id's.

Depends what you want to achieve in the end.

http://dev.mysql.com/doc/refman/5.0/en/join.html

Have fun

[webmasterjunkie]

I want to get how many items a user has posted in all tables of the database. Each table had a user_id field in it.

So a user can post 2 items in $job_table and 4 items in $housing_table. I want to be able to echo out that the user has 6 total items and what table they are in.

Sorry for not explaing it correctly. Also, I am not receiving any error, just keeps spitting out $total_items = 0 when I knwo there are some there. I just want this returned for one user_id which is based on a session.

[wsmeyer]

I know it is just part of the code but there isn't anything that sets a value for $user_id. If that is coming from another query echo that at the beginning of the script so you know it has a valid value.

On a side note, does the names of your tables really start with a $, I didn't even know that was possible.

William.

[webmasterjunkie]

Originally Posted by wsmeyer

I know it is just part of the code but there isn't anything that sets a value for $user_id. If that is coming from another query echo that at the beginning of the script so you know it has a valid value.

On a side note, does the names of your tables really start with a $, I didn't even know that was possible.

William.

The $user_id comes from a session:

$user_id = $cookie_info[0];

The table information is stored in a constants page:

$user_table = $db_pfix . "users";
$profiles_table = $db_pfix . "profiles";
etc...

[Easywebdev]

There is no need to select * as all you want is the occurances of the user_id in each table.

Use this.

<?php
   $db_connection = @mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
   @mysql_select_db($db_name, $db_connection) or die (mysql_error());

   $tables = array($job_table, $housing_table, $news_table, $profiles_table, $products_table, $research_table,
   $reviews_table, $services_table, $travel_table, $vehicles_table, $wanted_table);
   
   $total_items = 0;
   foreach($tables as $table)
   {
   	$sql = "SELECT COUNT(user_id) AS num_entries FROM $table WHERE user_id='$user_id'";
        $result = @mysql_query($sql, $db_connection) or die(mysql_error());
        $row = mysql_fetch_row($result);
        $total_items += $row[0]['num_entries'];
   }
   echo $total_items;

   mysql_close($db_connection);   
?>

You should not be storing your table names as variables. In your constants file define them instead, ie define("PROFILES_TABLE",$db_pfix . "profiles");

The above code sends more queries but as its a simple COUNT of a single column and you dont have the huge WHERE clause it should be quicker.

Hope that helps.

[icb01co2]

Easywebdev - Was about to post a solution similar till i read yours, I like your solution its clean and elegant. Thats all i wanted to say, lol.