Welcome Guest ( Log In | Register )



 
 Reply to this topicStart new topic
> Selecting More Than One Table
khalilov
post Jul 24 2008, 01:00 PM
Post #1


Member [ Level 2 ]
Group Icon

Group: Members
Posts: 56
Joined: 17-July 08
Member No.: 31,503
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)
Go to the top of the page
 
+Quote Post
yordan
post Jul 24 2008, 01:51 PM
Post #2


Way Out Of Control - You need a life :)
Group Icon

Group: [MODERATOR]
Posts: 1,980
Joined: 16-August 05
Member No.: 7,896
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"
Go to the top of the page
 
+Quote Post
TavoxPeru
post Jul 26 2008, 04:08 AM
Post #3


Super Member
Group Icon

Group: [HOSTED]
Posts: 750
Joined: 8-April 06
From: Lima - Peru
Member No.: 12,579
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,
Go to the top of the page
 
+Quote Post
khalilov
post Jul 26 2008, 08:01 AM
Post #4


Member [ Level 2 ]
Group Icon

Group: Members
Posts: 56
Joined: 17-July 08
Member No.: 31,503
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.
Go to the top of the page
 
+Quote Post
yordan
post Jul 26 2008, 11:24 AM
Post #5


Way Out Of Control - You need a life :)
Group Icon

Group: [MODERATOR]
Posts: 1,980
Joined: 16-August 05
Member No.: 7,896
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.
Go to the top of the page
 
+Quote Post
khalilov
post Jul 26 2008, 05:47 PM
Post #6


Member [ Level 2 ]
Group Icon

Group: Members
Posts: 56
Joined: 17-July 08
Member No.: 31,503
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?
Go to the top of the page
 
+Quote Post
yordan
post Jul 27 2008, 10:31 AM
Post #7


Way Out Of Control - You need a life :)
Group Icon

Group: [MODERATOR]
Posts: 1,980
Joined: 16-August 05
Member No.: 7,896
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. rolleyes.gif
Go to the top of the page
 
+Quote Post
« Next Oldest · Databases · Next Newest »
 

Reply to this topicStart new topic

Collapse

> Similar Topics

Topics Topics
  1. Anyone Play True Rpg's(8)
  2. Creating A Css Rollover Menu (with Table Cells)(9)
  3. Table Layout Vs. Css Layout(18)
  4. New Periodic Table Of The Elements(24)
  5. Problems With A Database Table & PHP Syntax(21)
  6. Printing Out A Table(6)
  7. Allinfobarn Network - New Online Moneymaker(1)
  8. Generating A Table Into A File In CSV Format(6)
  9. Change Table Colors On Mouse Effects!(8)
  10. Ever Needs To Find Out A Table Height Or With With JavaScript(2)
  11. Specific Keywords To Create Table?(7)
  12. Count The Number Of Records Of A Table Group By A Foreign Key(0)
  13. Free Secondary Dns/backup Name Server List(0)
  14. Re-order MySQL Table(11)
  15. How To Show Serial Nums In PHP Table For Contents Of MySQL DB(4)
  1. Distributed Hash Table Algorithm(0)
  2. Table Issues(4)
  3. Problem With Selecting A Textbox Content(0)
  4. Problems Dynamically Adding A Table With Dom(4)
  5. Html Table Issue.(18)
  6. Html Table Rows Problem [solved](1)


 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

- Lo-Fi Version Time is now: 7th September 2008 - 11:03 AM
Powered By IP.Board 2.2.1 © 2008  IPS, Inc.
Licensed to: Xisto Corporation