[Sportznow]

Hello, I want to use php to sort through multiple tables in a mysql database and have them be in order by a certain field. However, I can't seem to get it to sort through multiple tables. As of now it prints out the multiple tables in order, but doesn't mix the results together in the tables. Here is the code:

<?
function db_connect(){
$hostname = "*****";
$username = "*****";
$password = "*****";
$dbName = "******";

mysql_connect($hostname, $username, $password) or die("DB connection unavailable");
mysql_select_db( "$dbName") or die( "Unable to select database");
}

function search_db($usertable){
//query details table begins
$sql = "SELECT * ";
$sql .= "FROM $usertable ";
$sql .= "ORDER BY `Player` ASC LIMIT 0, 30 ";

$result = mysql_query($sql) or die("Query failed");

while($row = mysql_fetch_array($result))
{
$variable1=$row["Player"];
$variable2=$row["Avg"];
$variable3=$row["HR"];
$variable4=$row["RBI"];
$variable5=$row["Runs"];
$variable6=$row["SB"];
$variable7=$row["CS"];
$variable8=$row["OBP"];
$variable9=$row["AB"];

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td><div align=center>$variable2</div></td>");
print ("<td><div align=center>$variable3</div></td>");
print ("<td><div align=center>$variable4</div></td>");
print ("<td><div align=center>$variable5</div></td>");
print ("<td><div align=center>$variable6</div></td>");
print ("<td><div align=center>$variable7</div></td>");
print ("<td><div align=center>$variable8</div></td>");
print ("</tr>");
}
}
db_connect();
search_db('Yankees');
search_db('Anaheim');
search_db('Arizona');
?>

Can someone please give some help?

[Snake~eyes]

So you want the data to sort it all together?


Info from Yankees, Anaheim and Airzona to all mix together in order?

[deuce868]

Make sure that the player field is just like that. Caps on the first letter. Try leaving out the `. I never end up using them in php queries.

Now if you are trying to sort all three tables together by that field so that you would have Arizona players mixed with Yankee players all sorted by Player, you need to add some more code to take all three results and sort them manually with a manual sort function.

[Sportznow]

Yes, I want the results mixed all together. How would I go about doing this?

[Snake~eyes]

Sounds to me like your table structure is wrong then. Why create a thing for each team? Just use the same structure as one of the teams and add an extra column that says team. Or better yet have a table with all the teams and cross reference the id into the other tables.

[deuce868]

you would have to do all your queries and put all of the results into a single giant array. Look up sort on php.net and there is a way to create a custom sort function. Once you have it sorted you will have to look through your entire array and print them out to get them mixed.

[Sportznow]

well, I have queries that use only certain teams, that is why I don't use one big table. I will look that up on PHP.Net, thankyou.

[Guy]

I agree with snake~eyes that your table structure could be better defined, but if you want to use your existing structure, this should help. NOTE: you will need mysql 4.0 or higher and I havnt tested this at all so there my be some errors

PHP Code:

function search_db($tables)
{
  $sql = "";
  foreach( $tables as $usertable )
  {
    if( $sql != "" )
      $sql .= "UNION ";
    $sql .= "(SELECT * FROM $usertable ORDER BY `Player` ASC LIMIT 0, 30) ";
  }
  $sql .= "ORDER BY `Player`";
    

  $result = mysql_query($sql) or die("Query failed");

  while($row = mysql_fetch_array($result))
  {
    $variable1=$row["Player"];
    $variable2=$row["Avg"];
    $variable3=$row["HR"];
    $variable4=$row["RBI"];
    $variable5=$row["Runs"];
    $variable6=$row["SB"];
    $variable7=$row["CS"];
    $variable8=$row["OBP"];
    $variable9=$row["AB"];

    print ("<tr>");
    print ("<td>$variable1</td>");
    print ("<td><div align=center>$variable2</div></td>");
    print ("<td><div align=center>$variable3</div></td>");
    print ("<td><div align=center>$variable4</div></td>");
    print ("<td><div align=center>$variable5</div></td>");
    print ("<td><div align=center>$variable6</div></td>");
    print ("<td><div align=center>$variable7</div></td>");
    print ("<td><div align=center>$variable8</div></td>");
    print ("</tr>");
  }
}

db_connect();
search_db( array('Yankees', 'Anaheim', 'Arizona') ); 


[Snake~eyes]

Originally Posted by Sportznow

well, I have queries that use only certain teams, that is why I don't use one big table. I will look that up on PHP.Net, thankyou.

If you have queries for certain teams all you do is do

SELECT * FROM teams WHERE teamname='$team'