Jump to content

Multiple SELECT queries combined


master82

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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());
?>

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

<?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`");
?>

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.