Merge Two MySQL Queries in one Array

Question

Purpose of this query is to retrieve a true image plus 3 random generated images from same table, then show them randomly. User(child) have to select correct image.

Thanks

$sql= "SELECT * FROM `login` WHERE `user` = '$word'";
" UNION"
"SELECT * FROM `login` WHERE `user` != '$word' ORDER BY RAND() LIMIT 3";


$row2=mysql_query($sql);

$i=1;
while ($r = mysql_fetch_array($row2))
{
 echo '<td> ';
 echo '<img src="sigg/'.$r['img'].'" width="130" height="130" /><br>';
 echo $r['user'];
 echo '</td>';
   $i++;
 }

I see you significantly updated your question a while ago, but you aren't asking anything anymore. Can you re-read and re-write your question, please? It's a bit confusing now. — Petr R., Jul 27 '13 at 16:09
mysql_fetch_array() expects parameter 1 to be resource, boolean given in D:\xampp\htdocs\alb\do_search.php on line 22 — ZAW, Jul 27 '13 at 16:11
@ZAW - There is a mistake in your `$sql=....;` statement. Correct it *(or copy it from my answer)* and the error will be gone. — Petr R., Jul 27 '13 at 18:17

score 3 · Answer 1 · edited May 23 '17 at 12:14

Use the UNION clause:

$sql = "(SELECT * FROM `login` WHERE `user` = '$word')";
$sql.= " UNION";
$sql.= " (SELECT * FROM `login` WHERE `user` != '$word' ORDER BY RAND() LIMIT 3)";
$sql.= " ORDER BY RAND()";

To get the results you can use for example MySQLi (poseted before OP added his code with mysql_* functions):

$MySQL=new mysqli("localhost", "username", "password", "database");
$query = $MySQL -> query($sql);
while ($entry = $query -> fetch_row())
{
    // $entry is an array with the results, use for example:
    echo $entry[0]; // will return the first column of your table
    echo $entry[1]; // will return the second column of your table

    // try also:
    var_dump($entry); // outputs everything for testing purposes
}

Please, don't use the mysql_* functions, they are deprecated and will be removed in the future versions of PHP. Use MySQLi or PDO instead. See Why shouldn't I use mysql_* functions in PHP? for more details.

score 0 · Answer 2 · answered Jul 27 '13 at 15:43

Your request is not clear enough to provide a solid answer, so I'll try to answer it the best I can.

You should use Union in your query to get one big list of entries. However, just doing

SELECT * FROM `login` WHERE `user` = '$word'
UNION
SELECT * FROM `login` WHERE `user` != '$word' ORDER BY RAND() LIMIT 3

will give you a list of entries where user = $word in the first part and random 3 other entries.

As I said, I don't know the exact purpose of this, but I think you're better of querying the entire list from your database server.

score 0 · Answer 3 · edited May 23 '17 at 12:06

Here's the php code:

$connection = mysql_connect(HOST, USER, PASSWORD);
mysql_select_db(DATABASE_NAME, $connection);

$sql = "SELECT img, user FROM `login` WHERE `user` = '{$word}' UNION SELECT img, user FROM `login` WHERE `user` != '{$word}' ORDER BY RAND() LIMIT 3";

$results = mysql_query($sql, $connection);
$rows = array();

// Insert results in a new array to shuffle it
while ($row = mysql_fetch_array($results)) {
    $rows[] = array(
        'img' => $row['img'],
        'user' => $row['user']
    );
}

shuffle ($rows); // Randomize order

// Construct HTML
$html = '';
foreach ($rows as $entry) {
    $html .= '<td><img width="130px" height="130px" src="sigg/' . $entry['img'] . '">
    $html .= $user . '</img></td>';
}

echo $html;

You will need to replace capitalized words with what's necessary. A few explanations:

replace * with only what you need from tables (use fewer memory)
first insert results in an array so you can randomize the order (from MySQL you'll always have the first line the results from first query)
construct the html and output it all at once (executing multiple echo relies on the buffering to not send it to the browser, but that option might be off: What is output buffering).

Merge Two MySQL Queries in one Array

3 Answers3