Member Avatar for 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?

  • 4 Contributors
  • 8 Replies
  • 101 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Tekkno

Dani AI

Generated

A few practical points that tie the earlier replies together and help avoid the common traps shown here. The rows you fetch become associative arrays keyed by the selected column names (or the aliases you give them), not by a "table.column" string. If two tables provide the same field name, alias those columns to unique names. Test the SQL itself in a DB client first so you see any syntax or reserved-word errors. was right to point you at fetching rows; 's table/column aliasing is exactly what prevents name collisions; 's UNION idea is useful only when you want separate SELECTs stacked together; eventually tracked the issue to a reserved column name.

Quick checklist for debugging:

  • Run the query directly in phpMyAdmin or the mysql client to get precise error text.
  • If the query returns false from the API, inspect the DB error from your driver rather than continuing to fetch.
  • Quote/rename reserved column names (or use backticks), and prefer explicit JOIN ... ON clauses instead of comma-separated tables to avoid accidental cartesian products.
  • When collecting rows in PHP, push one associative row per loop so each array element represents a single record.

Example pattern using PDO (safe, simple to adapt). This avoids the older ext/mysql functions and keeps code readable:

$stmt = $pdo->query(
  "SELECT t1.title AS title_a, t2.title AS title_b
   FROM table1 t1
   JOIN table2 t2 ON t1.id = t2.t1_id"
);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo '<tr><td>' . htmlspecialchars($row['title_a']) . '</td><td>' . htmlspecialchars($row['title_b']) . '</td></tr>';
}

Use prepared statements for any user input, and consider migrating away from ext/mysql (removed in newer PHP versions). PDO and mysqli provide reliable fetch methods and error reporting: see PDOStatement::fetchAll and mysqli_result::fetch_assoc.

Recommended Answers

All 8 Replies

Member Avatar for 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.

Member Avatar for 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.

Edited by Tekkno because: n/a
Member Avatar for 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.

Member Avatar for 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.

Edited by Tekkno because: n/a
Member Avatar for 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 />";
Member Avatar for 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

Member Avatar for Tekkno

Tried it dark but still no data being echoed.

Member Avatar for Tekkno

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.