Selecting More Than One Table

[khalilov 0]

I know you can connect to data base and select a table, can you select a table get what you want from it then select another table with the same command, or do you have to close the first table (is their a command for closing a table)

[yordan 10]

Have a look here : http://forums.xisto.com/topic/90708-topic/?findpost=
A nice topic concerning the way of selecting from severaly tables, for instance :

$sql = "SELECT t1.name, t2.location FROM users t1, profiles t2"

[TavoxPeru 0]

As yordan said you can select data from several tables by joining all of them in one single sql command, but you can also select some data from one table and then select some other data from another diferent table, for example, there are situations where you need to get some data from a table and then get some other data from another table that depends on one field of the first table, something like this:

 

<?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) {	die("Error, Can't connect to server: " . mysql_error());}// make foo the current db$db_selected = mysql_select_db('foo', $link);if (!$db_selected) {	die ("Error, Can't use db foo : " . mysql_error());}$tx="<table width='100%' border='1' valign='top' cellspacing='0' cellpadding='0'>";$resultOne=mysql_query("select * from tableOne",$link);$nRowsOne=mysql_num_rows($resultOne);if($nRowsOne>0){	while($rowOne = mysql_fetch_array($resultOne)) {		$nRowsTwo=0;		$j=0;		$idOne=$rowOne["id_One"];		$tx.="<tr>\n";		$tx.="<td width='100%' align='center' valign='middle'>\n";		$nameOne = trim($rowOne["Name"]);		$tx.=$nameOne;		$tx.="\n</td>\n</tr>\n";		$resultTwo=mysql_query("select * from tableTwo where (tableTwo.id_One=$idOne",$link);		$nRowsTwo=mysql_num_rows($resultTwo);		if($nRowsTwo>0)		{			$nRowsTwo=$nRowsTwo/4;			$jTwo=0;			while($jTwo<$nRowsTwo)			{				$tx.="<tr>\n";				$tx.="<td width='100%' align='center' valign='middle'>\n";				for ($h = 0; $h<4; $h++)				{					if($rowTwo = mysql_fetch_array($resultTwo))					{						$nameTwo = trim($rowTwo["name"]);						$tx.=$nameTwo;					}					else $tx.=" ";				}				$tx.="\n</td>\n</tr>\n";				$jTwo++;			}		}	}}$tx.="</table>\n";echo $tx;mysql_close($link);?>

There is no command to close a table, what you can do is to free up the memory used by your result data that you get from your sql command with the mysql_free_result() function.

 

mysql_free_result() will free all memory associated with the result identifier result.

 

mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution.

You can use the mysql_close() function to close the connection to your database.

 

mysql_close() closes the non-persistent connection to the MySQL server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is used.

 

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. See also freeing resources.

 

This simple example shows how to connect, execute a query, print resulting rows and disconnect from a MySQL database.

<?php// Connecting, selecting database$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')   or die('Could not connect: ' . mysql_error());echo 'Connected successfully';mysql_select_db('my_database') or die('Could not select database');// Performing SQL query$query = 'SELECT * FROM my_table';$result = mysql_query($query) or die('Query failed: ' . mysql_error());// Printing results in HTMLecho "<table>\n";while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {   echo "\t<tr>\n";   foreach ($line as $col_value) {	   echo "\t\t<td>$col_value</td>\n";   }   echo "\t</tr>\n";}echo "</table>\n";// Free resultsetmysql_free_result($result);// Closing connectionmysql_close($link);?>

Best regards,

[khalilov 0]

K thanks i think i got the idea guys =).I have another question, how do you view a the data in the table, i know i can do that in a while loop in a php script but i don't want to do that every time, i searched the data base in localhost and phpmyadmin and i just can't find it O.o, its probably something i missed. I want to view it dierectly from their.

[yordan 10]

K thanks i think i got the idea guys =).
I have another question, how do you view a the data in the table, i know i can do that in a while loop in a php script but i don't want to do that every time, i searched the data base in localhost and phpmyadmin and i just can't find it O.o, its probably something i missed. I want to view it directly from their.

If you cannot find the database in phpmyadmin, this means that the database is not where you think it is. If you are able to write the connect string for php, you should be able to provide the same infos to phpmyadmin and display the list of the tables and display a table content.

[khalilov 0]

If you cannot find the database in phpmyadmin, this means that the database is not where you think it is. If you are able to write the connect string for php, you should be able to provide the same infos to phpmyadmin and display the list of the tables and display a table content.

Found it =)

I had to enter database->select table-> then enter search =)

Iam guessing thats the only way?

[yordan 10]

Found it =)
I had to enter database->select table-> then enter search =)

Iam guessing thats the only way?

At least, it's the way I do it, I found no need to look for another way for doing that.

[iGuest 3]

SHOW ONLY CLOUMNS VALUES IN COMBO BOXSelecting More Than One Table

Hi,

I have a column which has the values like below11-AT-00111-AT-00220-AT-00441-AT-09145-AT-001I have created 2 combobox and I want each combo box to show me only some record of column.

For example I want Combobox 1 to show me only first two values (11-AT-001 & 11-AT-002) and Combobox 2 to show me last 3 values ( 20-AT-004, 41-AT-091, 45-AT-001). Please tell me which query I should use?

-question by aziz

[iGuest 3]

how to remove items from a column(combobox)using phpMyAdmin?Selecting More Than One Table

my actual table don't have values like that which appear in the combo box of table ,how can I remove those values ?pz help me out? I mean to say that their are some extra values(including null and some integer values) in the combo box and these values are not part of actual values which were entered in tha actual column,what can I do?

-question by hina