Member Avatar for websponge

Most annoyingly I cant get this to work, I made a sample table where there will be duplicate entries in some of the fields, and I need to display then all.

table looks a bit like this
id county city phonenumber

the id is unique as is the phonenumber, but there are records that would have the same county and city.. i need to search on county and display all the records for that county, but it only returns one record..

so I would want a result like the below, if my searh query was "cardiff"

1 cardiff llandaff 123
2 cardiff llandaff 456
3 cardiff pontypoo 856
4 cardiff pontypoo 966

hope you can help...

  • 4 Contributors
  • 17 Replies
  • 206 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by websponge

Dani AI

Generated

The situation reported by — phpMyAdmin returning multiple rows but the PHP page showing only one — is almost always a display-loop problem rather than a SQL problem. was right to advise checking the raw query, and ’s trailing-space suggestion is a useful diagnostic. correctly pointed out that output belongs inside the fetch loop. The most common root cause is assigning fetched columns to variables repeatedly and only echoing them after the loop, which leaves the last row’s values displayed.

Practical fixes and a safer workflow:

  • Output each row as it is fetched, or collect rows into an array and then iterate that array for output.
  • Trim and validate the incoming form value before using it so accidental whitespace does not break matches.
  • Avoid the old mysql_* extension (used in the original posts); use PDO or mysqli with prepared statements to prevent SQL injection and to make debugging easier.

Example (PDO, prepared statement, safe output):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=project;charset=utf8mb4', 'dbuser', 'pass',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$stmt = $pdo->prepare('SELECT id,county,city,phone FROM area WHERE county = :county');
$stmt->execute([':county' => trim($_POST['county'] ?? '')]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $r) {
    echo htmlspecialchars($r['id']).' '.htmlspecialchars($r['county']).' '.
         htmlspecialchars($r['city']).' '.htmlspecialchars($r['phone'])."<br>\n";
}
?>

Quick debugging checklist: echo the raw SQL (as suggested) and run it in phpMyAdmin; check the fetched-row count (use count($rows) after fetchAll); var_dump a single fetched row to confirm fields; and inspect/truncate trailing whitespace with trim() if needed. Finally, escape output with htmlspecialchars for safe HTML rendering and migrate away from deprecated APIs for long-term stability.

Recommended Answers

All 17 Replies

Member Avatar for websponge

my search string is this:

$result = mysql_query("SELECT * FROM area WHERE county = '$county'");

$county coming from a form...

Member Avatar for Szabi Zsoldos

Try this

$result = mysql_query("SELECT * FROM area WHERE county = '".$county."'");

But the query looks allright, try to run your query from the phpmyadmin or the administrative tool you have, and see the results there first.

Member Avatar for websponge

still will only display one result :( ive been on this for over an hour.. thanks for the reply though

Member Avatar for websponge

actually, it works in phpmyadmin!!

Member Avatar for Szabi Zsoldos

echo the $result without mysql_query and see the query, and then run that query in phpmyadmin

Member Avatar for paulkd

Might your other records may contain a trailing space?

Member Avatar for websponge

No trailing spaces, if I echo the result I get "Resource id #4"

Member Avatar for Szabi Zsoldos

echo it without mysql_query

Member Avatar for websponge

how can i echo a result if there is no query results?

Member Avatar for Szabi Zsoldos 
$result = ("SELECT * FROM area WHERE county = '".$county."'");

echo $result;
Member Avatar for websponge

SELECT * FROM area WHERE county = 'cardiff'

Member Avatar for websponge

hi, did that, see above, no change :(

Member Avatar for Szabi Zsoldos

post all your code :))

Member Avatar for websponge 
<?php
echo "<h1>Results Summary</h1>";
//TABLE RESULTS
$county = $_POST['county'];
$con = mysql_connect("localhost","root","");
mysql_select_db('project', $con) or die('Cant Connect');
$result = mysql_query("SELECT * FROM area WHERE county = '".$county."'");
while($row = mysql_fetch_array($result))
{
$id = $row['id'];
$county = $row['county'];
$city = $row['city'];
$phone = $row['phone'];
}
echo $id;
echo $county;
echo $city;
echo $phone;
?>
Member Avatar for pritaeas

The echo's should be inside the loop...

Member Avatar for Szabi Zsoldos

Yes, in this case you are echoing the last row from the database because your script continues after the while loop.

As said, you have to echo inside your while

Edited by Szabi Zsoldos
Member Avatar for websponge

How incredibly stupid of me..... thank you both very much, I feel a bit dull..

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.