Basic PHP SQL Query not working

Question

We have a basic PHP script to extract the title and description for each job from a MySQL database as simply display this information. This is what it looks like:

$sql        = "SELECT `title`, `desc` FROM jobs WHERE active = 'y'";
$query      = mysql_query($sql) or die('<em><strong>SQL Error:</strong> ' . mysql_error() . '</em>');
$results    = mysql_fetch_assoc($query);

<?php while($result = mysql_fetch_assoc($query)) {
    echo '<div class="left_content" style="margin-top: 15px;">';
    echo "<h2>{$results['title']}</h2>";
    echo "<p>{$results['desc']}</p>";
    echo '</div>';
} ?>

Now, this only extracts one row from the database, but it should extract two. So, I tried the following to replace the while statement:

<?php foreach($results as $result) {
    echo '<div class="left_content" style="margin-top: 15px;">';
    echo "<h2>{$result['title']}</h2>";
    echo "<p>{$result['desc']}</p>";
    echo '</div>';
} ?>

This statement doesn't work either. This just displays (weirdly) the first character of each column in the first row in the table.

Does anyone have any idea as to why this isn't working as it should?

Mihai Iorga · Accepted Answer · 2012-10-09 10:07:49Z

3

In your while use same variable $result as you started:

while($result = mysql_fetch_assoc($query)) {
    echo '<div class="left_content" style="margin-top: 15px;">';
    echo "<h2>{$result['title']}</h2>";
    echo "<p>{$result['desc']}</p>";
    echo '</div>';
}

and remove the first $results = mysql_fetch_assoc($query);

answered Oct 9, 2012 at 10:07

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iLaYa ツ · Accepted Answer · 2012-10-09 12:19:56Z

2

Result variable you have used is result not results

Replace

$sql        = "SELECT `title`, `desc` FROM jobs WHERE active = 'y'";
$query      = mysql_query($sql) or die('<em><strong>SQL Error:</strong> ' . mysql_error() . '</em>');
**$results    = mysql_fetch_assoc($query);** // remove this line

<?php while($result = mysql_fetch_assoc($query)) {
    echo '<div class="left_content" style="margin-top: 15px;">';
    echo "<h2>{$results['title']}</h2>";
    echo "<p>{$results['desc']}</p>";
    echo '</div>';
} ?>

to

$sql        = "SELECT `title`, `desc` FROM jobs WHERE active = 'y'";
$query      = mysql_query($sql) or die('<em><strong>SQL Error:</strong> ' . mysql_error() . '</em>');


<?php while($result = mysql_fetch_assoc($query)) {
    echo '<div class="left_content" style="margin-top: 15px;">';
    echo "<h2>{$result['title']}</h2>";
    echo "<p>{$result['desc']}</p>";
    echo '</div>';
} ?>

edited Oct 9, 2012 at 12:19

answered Oct 9, 2012 at 10:07

iLaYa ツ

4,0173 gold badges34 silver badges51 bronze badges

1 Comment

Daniel Groves Over a year ago

Head-desk. Thanks so much, don't know how I missed that. $results was only in there still from experimentation… Can only kick myself for not deleting it straight away as then I would have spotted it for myself.

Burhan Khalid · Accepted Answer · 2012-10-09 10:08:46Z

1

You already fetched the first row before your loop started, which is why it only prints the second row. Simply comment out that line:

#$results = mysql_fetch_assoc($query); # here is your first row, 
                                       # simply comment this line

<?php while($result = mysql_fetch_assoc($query)) {
    echo '<div class="left_content" style="margin-top: 15px;">';
    echo "<h2>{$result['title']}</h2>";
    echo "<p>{$result['desc']}</p>";
    echo '</div>';
} ?>

You are also looping over $result but using $results in your while loop body.

answered Oct 9, 2012 at 10:08

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

Comments

Somnath Muluk · Accepted Answer · 2012-10-09 10:09:11Z

0

Check this:

$sql        = "SELECT `title`, `desc` FROM jobs WHERE active = 'y'";
$query      = mysql_query($sql) or die('<em><strong>SQL Error:</strong> ' . mysql_error() . '</em>');


<?php 
while($result = mysql_fetch_assoc($query)) {
    echo '<div class="left_content" style="margin-top: 15px;">';
    echo "<h2>{$result['title']}</h2>";
    echo "<p>{$result['desc']}</p>";
    echo '</div>';
}
 ?>

answered Oct 9, 2012 at 10:09

Somnath Muluk

58.3k39 gold badges225 silver badges230 bronze badges

Comments

Rohit Choudhary · Accepted Answer · 2012-10-09 10:10:55Z

0

Change this line

<?php while($result = mysql_fetch_assoc($query)) {

to

<?php while($results = mysql_fetch_assoc($query)) {

answered Oct 9, 2012 at 10:10

Rohit Choudhary

2,2571 gold badge24 silver badges35 bronze badges

Collectives™ on Stack Overflow

Basic PHP SQL Query not working

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related