Can't get mysql_fetch_rows to cooperate

Question

I'm trying to retrieve rows from a mysql table based on variables input by the user. Basically if use wants to search for "bob dilan" $fname and $lname would search and display all rows where this takes place, my connection to the user is fine as my $mysql_num_fields is working perfectly. Currently nothing works inside of my mysql_fetch_rows while loop, not even the echos, but there are no errors. I have even tried using the like operator instead of = to no avail.

PHP

$sql = "SELECT * FROM  ".$tbl_name." 
WHERE fname='".$fname."' 
and lname='".$lname."' 
and city='".$city."'
and phone='".$pohne."'
and interest_inet='".$internet."'
and interest_tv='".$television."'
and interest_voice='".$voice."'
and submission_ip='".$ip."'
and inquiry_handled='".$handled."'";
$result = mysql_query($sql);

echo "<table border='1'>";
echo "<tr>";
$i = 0;

while ($i < mysql_num_fields($result))
{
    $meta = mysql_fetch_field($result, $i);   
    echo "<th>".$meta->name."</th>";
    $i++;
}

while ($row = mysql_fetch_row($result))
{
    echo '<tr>';

foreach($row as $item)
{
    echo "<td>".$item."</td>";
}

echo '</tr>';
echo $row;
}

echo "</table>";

Welcome to Stack Overflow! [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Madara's Ghost, Jan 06 '13 at 20:49

score 4 · Accepted Answer · edited May 25 '19 at 11:53

4

You're already iterating through the resultset once, which causes the pointer to advance to the end of it.

Complete all of your work in a single loop, or fetch the entire resultset into an array, and iterate it twice.

Other than that, you shouldn't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 11:53

Zoe

27,060
21
118
148

answered Jan 06 '13 at 20:52

Madara's Ghost

172,118
50
264
308

I think im missing what you mean, there are two seperate while loops, the first displays column names and the second SHOULD echo rows in which $sql is true, but its not. hence the problem – Nick Jan 06 '13 at 20:56
1

@JohnDoe: Once a query is run, a resultset is returned. A pointer in memory is set on the first item. When you loop, the resultset pointer automatically advances (That's why you don't need to increment a variable or anything when looping). After you're done, there's nothing to place that pointer back at the first item, so further calls to `mysql_fetch_*` will return nothing. – Madara's Ghost Jan 06 '13 at 21:02
Real quick question that is bothering me a bit. if i had a value for fname, but nothing was posted over for the other variables, it should still echo results frop the table whereever fname matches correct? – Nick Jan 06 '13 at 21:17
@JohnDoe: No. Since you're applying `AND` between each condition, it will only work if all of the other fields are empty. You are perhaps looking for `OR`. – Madara's Ghost Jan 06 '13 at 21:18
I considered OR, but OR just displays the entire table – Nick Jan 06 '13 at 21:21
@JohnDoe: In that case, you want to build your query in a way so that only included terms are placed in the query. I've seen scripts like those running around here on [so], and if you can't find any, you can always try to implement it yourself. – Madara's Ghost Jan 06 '13 at 21:23
I had an idea for what, but it was 72 functions long, but this way is clearly not going to work for me. guess i ought to buckle down and get to it. thanks. – Nick Jan 06 '13 at 21:26

score 0 · Answer 2 · answered Jan 06 '13 at 21:04

The other way to solve this problem is to use mysql_data_seek

while ($i < mysql_num_fields($result))
{
    $meta = mysql_fetch_field($result, $i);   
    echo "<th>".$meta->name."</th>";
    $i++;
}

mysql_data_seek($result);

while ($row = mysql_fetch_row($result))

Can't get mysql_fetch_rows to cooperate

2 Answers2