Multiple SELECT queries combined

[master82]

I have several SELECT queries on a few of my webpages, and I have been told that the more query connections that are open the slower the server will run.

 

So firstly, is it possible to merge/combine 2 SELECT queries into a single query?

 

Current I use 2 or more like below:

$a=mysql_query=("SELECT fields FROM table WHERE userid = '{$_SESSION['userid']}'");
$aa=mysql_fetch_assoc($a);
$b=mysql_query=("SELECT fields FROM table WHERE userid = '{$_SESSION['userid']}'");
$bb=mysql_fetch_assoc($b);

//then print the values within the page where needed
print $aa['field'];
print $bb['field'];

 

can they be combined? maybe something such as below (except im not sure about the WHERE statement)

$a=mysql_query("SELECT a.fieild, b.field FROM a.table, b.table WHERE a.userid = '{$_SESSION['userid']}', b.userid = '{$_SESSION['userid']}'");
$aa=mysql_fetch_assoc($a);
//print values when needed
print $aa['a.field']
//although im sure i dont need the a. above, and with both tables having different field names there should be no name conflict

 

And secondly, if the above is possible, would this reduce the number of connections to the SQL database and therefore save some of the servers resources?

[trq]

Why do you have two in the first place? There both identical

 

Post your actual queries and maybe we can help. Hint, queries cannot be combined using php's mysql extension, but, if your data is related, you may be able to use a JOIN.

[pocobueno1388]

As Thorpe said, it's kinda hard to give you a good example without having all the query information.

 

Here is an example of what it may look like:

 

<?php

mysql_query("SELECT
               t1.col1,
               t1.col2,
               t1.col3,
               t2.col1,
               t2.col2
            FROM
               table_1 t1,
               table_2 t2
            WHERE
               t1.userid = '{$_SESSION['userid']}' AND
               t1.userid = t2.userid
            ") or die (mysql_error());
?>

[master82]

Yes I currently have several queries....

 

Note the session stores the unique users id obtained from logging in

 

$ud=mysql_query("SELECT userid, username, bla bla bla FROM users WHERE userid = '{$_SESSION['userid']}'");

$user=mysql_fetch_assoc($ud);

 

$wd=mysql_query("SELECT userid, points, bla bla bla FROM wealth WHERE userid = '{$_SESSION['userid']}'");

$wealth=mysql_fetch_assoc($wd);

 

print"Welcome {$user['username']} you currently have {$wealth['points']} points available to use today";

 

Thats just a quick example...

[Nhoj]

<?php
mysql_query("
SELECT
`table1`.`col1`,
`table1`.`col2,
`table2`.`col1
FROM `table1`
WHERE `table1`.`userid` = '{$_SESSION['userid']}'
INNER JOIN `table2` ON `table1`.`userid` = `table2`.`userid`");
?>

 

 

 

It's called innerjoin.

 

<?php
mysql_query("
SELECT
`users`.`userid`,
`users`.`username`,
`wealth`.`points
FROM `users`
WHERE `users`.`userid` = '{$_SESSION['userid']}'
INNER JOIN `wealth` ON `users`.`userid` = `wealth`.`userid`");
?>

 

You could also extend it to as many tables as you want... (Make SURE you index them properly, i.e. for these the userid column would probably want to be either a primary key or an index.)

 

<?php
mysql_query("
SELECT
`users`.`userid`,
`users`.`username`,
`wealth`.`points,
`profile`.`email`,
`addresses`.`city`
FROM `users`
WHERE `users`.`userid` = '{$_SESSION['userid']}'
INNER JOIN `wealth` ON `users`.`userid` = `wealth`.`userid`
INNER JOIN `profile` ON `users`.`userid` = `profile`.`userid`
INNER JOIN `addresses` ON `users`.`userid` = `addresses`.`userid`");
?>

[master82]

so for 3 tables...

 

<?php
mysql_query("SELECT `table1`.`col1`,
           `table1`.`col2,
           `table2`.`col1,
            'table3`.`col2
FROM `table1`
WHERE `table1`.`userid` = '{$_SESSION['userid']}'
INNER JOIN `table2` ON `table1`.`userid` = `table2`.`userid`
INNER JOIN `table3` ON `table1`.`userid` = `table3`.`userid`");
?>

???

 

i only ask as I have about 5 different tables that can make up some pages, currently using 5 different queries to obtain the values using the same unique userid field in each

 

[Nhoj]

See my last post, i edited it....

[master82]

Thank you... You are all stars!

 

Lastly, before I go make changes....

 

Is it better to combine them, or would having them seperate be better?

 

(maybe reducing the number of connections saves on server CPU?)

[Nhoj]

It's actually not that bad to join them like that as long as they are properly indexed. You can run the query in phpMyAdmin with the word "EXPLAIN" at the beginning and it'l tell you whats going on.

 

Example ("EXPLAIN SELECT ..........")

 

If your "Extra" colum displays "Using filesort" your probably not indexing them very well....

[master82]

Thank you!