Need help creating an array

[Tekkno]

I am not really sure where to go with this, but any help I can get would be appreciated.

What I am trying to do is put together an array to use with a foreach loop by getting columns from multiple tables. Here is my query:

$results = mysql_query("SELECT column1,column2,column3 FROM table1,table2,table3,table4");

I think I need an associative array but am not quite sure how to put it together. The data would be in columns as it would be in the table just merged. Is this possible to do?

[darkagn]

$results is a resource that can be used to grab an array using any of the mysql_fetch functions:

while(($nextRow = mysql_fetch_assoc($results)) !== false)
{
   $column1 = $nextRow["column1"];
   // etc
}

The loop will stop when there are no more records to be fetched.

[Tekkno]

Thanks for the info dark. I believe I already tried that approach, when I use that I get:
"mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource".

After doing some debugging, it looks like the query is part of the problem. Does the syntax look ok? Would it matter if any of the columns are empty?

Just to double check I made sure those columns exist in all the tables, and that everything is spelled correctly.

[kylegetson]

Are you using a database management tool of some kind? Like phpMyAdmin? If so, test the query there, it'll give you more specific error messages. I usually label my queries tables by letter, to avoid confusion, because if your tables have field names the same, you could run into problems.

SELECT A.col1, B.col2, C.col3 FROM table1 as A, tabe2 as B, table3 as C

hope that helps.

[Tekkno]

Thanks for the help kyle. With your suggestion I was able to get the query to run properly. The problem was mysql was looking at the desc column as descending and didn't know what to do. However, even though I have the query working I am still not able to output any data. Lets use this as an example:

$results = mysql_query("SELECT a.column1, a.column2, a.column3 FROM table1 as a");
while(($nextRow = mysql_fetch_assoc($results)) !== false)
{
   $column1 = $nextRow["a.column1"];
}

How do I output columns from that?
It should look like:
column1 column2 column3
record record record
record record record
record record record
I would assume in order to get that I will need to do the while loop within html.

[darkagn]

Yes that's right. This might help:

$results = mysql_query("SELECT a.column1, a.column2, a.column3 FROM table1 as a");
while(($nextRow = mysql_fetch_assoc($results)) !== false)
{
      $data[]["col1"] = $nextRow["column1"];
      $data[]["col2"] = $nextRow["column2"];
      $data[]["col3"] = $nextRow["column3"];
}
echo "<br /><table border='1'><tr><th>column1</th><th>column2</th><th>column3</th></tr>";
foreach($data as $row)
{
    echo "<tr><td>{$row["col1"]}</td><td>{$row["col2"]}</td><td>{$row["col3"]}</td></tr>";
}
echo "</table><br />";

[guru12]

Hello darkgan

Try Union operator in mysql query

Syntax:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
or
(SELECT * FROM t1 )
UNION
(SELECT * FROM t2 )

Reference link
http://www.w3schools.com/sql/sql_union.asp

Thanks and Regards

[Tekkno]

Tried it dark but still no data being echoed.

[Tekkno]

I was able to get it working a much simpler way, it was an error on my part. Thanks for all your help.