Selecting More Than One Table

khalilov

Jul 24 2008, 01:00 PM

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)

Comment/Reply (w/o sign-up)

yordan

Jul 24 2008, 01:51 PM

Have a look here : http://www.astahost.com/index.php?showtopi...mp;#entry126095
A nice topic concerning the way of selecting from severaly tables, for instance :

QUOTE

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

Comment/Reply (w/o sign-up)

TavoxPeru

Jul 26 2008, 04:08 AM

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:

CODE

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

QUOTE

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.

QUOTE

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.

CODE

<?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 HTML
echo "<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 resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

Best regards,

Comment/Reply (w/o sign-up)

khalilov

Jul 26 2008, 08:01 AM

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.

Comment/Reply (w/o sign-up)

yordan

Jul 26 2008, 11:24 AM

QUOTE(khalilov @ Jul 26 2008, 10:01 AM)

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.

Comment/Reply (w/o sign-up)

khalilov

Jul 26 2008, 05:47 PM

QUOTE(yordan @ Jul 26 2008, 02:24 PM)

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?

Comment/Reply (w/o sign-up)

yordan

Jul 27 2008, 10:31 AM

QUOTE(khalilov @ Jul 26 2008, 07:47 PM)

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.

Comment/Reply (w/o sign-up)