How do i select two tables from the same database in one db connection?
Printable View
How do i select two tables from the same database in one db connection?
"select a table" isn't a technical term, I don't think.
You can select a database (activate one particular database from a single connection) and change that if you want. Just use mysql_select_db() again.
Or if you want to get information out of multiple tables, you can use a SELECT statement two times. That's easy-- just change the FROM table:
SELECT * FROM `table` ..... WHERE.....
OK lets say i have this:
<?php
$databasename='*********'; // Name of the database
$tablename='jobadd'; // Name of the table
$mysqladd='*********'; // Address to the MySQL Server
$mysqluser='**********'; // Your MySQL UserName
$mysqlpass='*******'; // Your MySQL Password
//CONNECT TO MYSQL
$link=mysql_connect($mysqladd, $mysqluser, $mysqlpass, true) or die('Could not connect to database: ' . mysql_error());
//CONNECT TO DATABASE
mysql_select_db($databasename, $link) or die('Could not connect to table: ' . mysql_error());
$query="SELECT * FROM jobadd";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) :
?>
Could i just change this to:
<?php
$databasename='*********'; // Name of the database
$tablename='jobadd'; // Name of the table
$mysqladd='*********'; // Address to the MySQL Server
$mysqluser='**********'; // Your MySQL UserName
$mysqlpass='*******'; // Your MySQL Password
//CONNECT TO MYSQL
$link=mysql_connect($mysqladd, $mysqluser, $mysqlpass, true) or die('Could not connect to database: ' . mysql_error());
//CONNECT TO DATABASE
mysql_select_db($databasename, $link) or die('Could not connect to table: ' . mysql_error());
$query="SELECT * FROM jobadd";
$query="SELECT * FROM svar";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) :
?>
1. Your $tablename='...'; line at the top is completely irrelevant-- that's just storing a name but it's never used anywhere. You can delete it, or I guess you can keep it if you want to make a note for yourself.
2. MySQL connections require a server, a username, and a password. Then in order to use anything you should select a database (or you can technically specify it in each query). That's fine and it works in your code, but you don't set a table as part of your configuration. Once this connection is established, you can continue using it as much as you want. You never need to reopen a database connection to run multiple queries. The only time you'd do that is if you were connecting to a different database (and that's generally a bad idea-- too many server resources and confusing to code).
3. Your original code has a sequence of commands that get you information out of a single table. You can duplicate the query (like you did) and that syntax is correct, but you will need to duplicate ALL of the commands. In fact, you didn't post all of your code. For example, what happens in the while loop?
4. If you're not sure how to proceed, then post all of your code. (Continue to obscure your passwords, though-- there's no need to post them here.) If you want to try it on your own, then duplicate the entire code starting with the $query line all the way through the end of your while loop and maybe more-- wherever you're dealing with the results.
In general, the way that data is organized in a database is that information you you want to access together is stored in a single table. Why do you want to get it from two tables? You can certainly do two separate queries that work separately. But if you're trying to get a single set of results from two independent tables, then that is a lot more complicated.
There are generally two ways to go about it:
1. You can use PHP to do two different MySQL queries, then organize the results in PHP and merge them that way. For example, you could get all of the information from one table, put it into an array, then get all of the information from the second table, add that to the same array, then in the end use the PHP array you created instead of a MySQL query as the base for your final while loop to display the info.
2. You can look into some more advanced MySQL commands like JOIN but honestly that's going to be confusing.
The other option is to rethink your database structure entirely and put everything in one table (must easier). One option is to add a new column the table, called "type" and you can have "type=1" for items originally from table1 and "type=2" for table2. That way you can still keep the info separate, but it's also in one place if needed.
If you need more help, please explain:
1. What you are trying to do (and why)
2. What your database structure looks like-- most importantly, do the two tables have identical structure? Are you getting many bits of info or just a single bit of info (one cell) from each row?
3. What is the rest of your code (the while loop and below)?
But if you're trying to get a single set of results from two independent tables, then that is a lot more complicated.
I have one table "jobadd" in vich i have a column "price" and then i have a table "svar" whare i have a script to echo a different price based on the price in the jobadd table. Like if the price in the jobadd table is 1500 then the echoed price shold bee 100. The data in "jobadd" are put there from a mailform and the data in "svar" is put there from another mailform. And then i have a 2 pages whare admin can viwe the data in thees tables separatly, this is wy i dont whant a single table. I need it to bee an easy overviwe of thees database tables.
Thank you so much for your answer i'l try this and if i dont get it work i'l get back to you and answer all your questions
THANK'S
I'm still not exactly sure what the best way to approach this is because I don't completely understand your data situation, but here's some example code that might help. This is a standard method.
The idea is that within the loop for the first query, you look up matching information with a second query.PHP Code:<?php
//CONNECT TO MYSQL DB FIRST
$query = mysql_query('SELECT * FROM `table1`'); //first query, from your first table
while ($row = mysql_fetch_assoc($query)) { //loop through each row from that result
$query2 = mysql_query('SELECT * FROM `table2` WHERE `column`='.$row['price']); //now search for the matching row in table 2 based on the price of this row in table 1
if ($row2 = mysql_fetch_assoc($query2)) { //if there's a matching result
echo "The price is: ".$row['price'].' and price2 is: '.$row2['price']; //"The price is $1500 and price2 is $100"
}
}
For example, you could have a table of employees. Then you could also have a table of office numbers. By looping through the employees, for each one you could look up the office number then display them together.
Again, sometimes it is easier to store everything in one table if that is possible. But if not, you can put one query inside another in this way, by organizing the PHP like the example.
I'v got this working now, THANK YOU.
Hey, Could you take a look on my latest Thread in the Mysql section?
Thank's