Thread: How to collect data from multiple tables?

How can I collect data from multiple tables on bluevoda? I am trying to get data from 2 tables from 1 database but I tried a few ways but didn't work, how will I be able to collect the data from 2 tables in the same database?

you have to have a script on the page laying it out.

Karen

I'm not sure what you mean. The easy way is to retrieve the data from the two tables, separately, using "WHERE" clauses to make sure that you retrieve the right ones, then, once you have the data, you can display them in your page.

Since you are not givin any info on what you are trying to do, what scripts you are using etc, it is not easy to advise you.

Originally Posted by navaldesign

I'm not sure what you mean. The easy way is to retrieve the data from the two tables, separately, using "WHERE" clauses to make sure that you retrieve the right ones, then, once you have the data, you can display them in your page.

Since you are not givin any info on what you are trying to do, what scripts you are using etc, it is not easy to advise you.

Ok this is the php code this is one of the ways I was trying to retrieve data from 2 tables.

PHP Code:

$sql = "SELECT * FROM name,frame WHERE name='$user' or name='$us'"; 


The bolded words are the 2 tables I am trying to get data from. Only those 2 tables.

Originally Posted by navaldesign

I'm not sure what you mean. The easy way is to retrieve the data from the two tables, separately, using "WHERE" clauses to make sure that you retrieve the right ones, then, once you have the data, you can display them in your page.

Since you are not givin any info on what you are trying to do, what scripts you are using etc, it is not easy to advise you.

so would you be able to help me select two or more tables like I showed above? this would really help me out

Try

PHP Code:

$sql = "SELECT * FROM name, frame WHERE name.name=frame.name AND name.name='$user' OR name.name='$us'"; 


But this is just a general query, I don't know what your table structure is or if the data should be the same in both tables. Either way when selecting from move then one table, you have to explicitly say which column corresponds to what table.

you can collect data from the tables by:

$result = mysql_query ( "SELECT * FROM `table1` WHERE 1");
$result = mysql_query ( "SELECT * FROM `table2` WHERE 1");
you can then extrat the info from them by

$row = mysql_fetch_array($result);

Originally Posted by Watdaflip

Try

PHP Code:

$sql = "SELECT * FROM name, frame WHERE name.name=frame.name AND name.name='$user' OR name.name='$us'"; 


But this is just a general query, I don't know what your table structure is or if the data should be the same in both tables. Either way when selecting from move then one table, you have to explicitly say which column corresponds to what table.

how do I apply that code to this cause I tried but didn't work out so I think I did it wrong:

PHP Code:

<?php
$rec_limit = 10;
$conn = mysql_connect("localhost","user","password");
if (!$conn)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("oieieais_Comment", $conn);
$sql = "SELECT count(id) FROM $useri, $username WHERE name='$username' or name='$useri'";
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
$row = mysql_fetch_array($retval, MYSQL_NUM );
$rec_count = $row[0];
if( isset($_GET{'page'} ) )
{
   $page = $_GET{'page'} + 1;
   $offset = $rec_limit * $page ;
}
else
{
   $page = 0;
   $offset = 0;
}
$left_rec = $rec_count - ($page * $rec_limit);
$sql = "SELECT *".
       "FROM $useri, $username WHERE name='$username' or name='$useri'".
       " ORDER BY tm DESC LIMIT $offset, $rec_limit";
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($nt=mysql_fetch_array($retval, MYSQL_ASSOC)){
}
mysql_close($conn);
?>

Well I still don't know your table structure so I can't say exactly what your query should be, but heres some problems with your script

1) You are using variables for your tables in your sql query. While you can do this, your using the same variables as column values and neither have been set to anything. If you look closly at what I posted before the tables that you are selected need to be specified with the columns, look at the color coding.
"SELECT * FROM name, frame WHERE name.name=frame.name AND name.name='$user' OR name.name='$us'"
Blue is the table, red is the column your selecting

2) Your logic for getting the correct page is wrong, instead of doing

PHP Code:

if( isset($_GET{'page'} ) )
{
   $page = $_GET{'page'} + 1;
   $offset = $rec_limit * $page ;
}
else
{
   $page = 0;
   $offset = 0;
} 


you should do

PHP Code:

$page = $_GET['page'];
if($page < 1)
{
    $page = 1;
}
$offset = $rec_limit * ($page - 1); 


This will also leave $page for use in your links to the next page, and won't skip records 10-19 like your code will now.

3) I don't know if you were using $left_rec somewhere, but its not being used in that script, you can get rid of it.

4) When your retrieving your url variable with GET you are using curly brackets. While this is valid syntax, you should always try to follow normal programming convention and use square brackets with arrays.