Echo results of mysql query with php

Question

I have this query

$people = "SELECT name FROM people";
$people = mysql_query($people) or die(mysql_error());
$row_people = mysql_fetch_assoc($people);
$totalRows_people = mysql_num_rows($people);

I can echo the results within a unordered list using a while loop like this

<ul>

<?php {do { ?>

    <li><?php echo $row_people['name'];?></li> 

<?php } while ($row_people = mysql_fetch_assoc($people));}?>

</ul>

But I can't used this as my html does not allow it.

<ul>
    <li class="first">
        <a href="?name=kate">Kate</a>
    <li>
    <li class="second">
        <a href="?name=john"><img src="john.jpg" />John</a>
    <li>
    <li class="third">
        <a href="?name=max"><span>Max</span></a>
    <li>
</ul>

My question is how can echo the name that was retrieved from the database into the appropriate place within this html?

Thanks for your help.

Please clarify your question. You seem to be missing the <?php ?> tags in your code. However, are you saying that you want to output markup like what is in the block of markup? Meaning, you're asking how to adjust it to suit this markup? — Jared Farrish
– Jared Farrish, Commented Oct 2, 2011 at 18:52
This code: <?php echo $row_people['name'] ?> is a XSS security hole. Always escape all output that you echo to html using the function htmlentities(), like so: <?php echo htmlentities($row_people['name']); ?> — Johan
– Johan, Commented Oct 2, 2011 at 19:05
I have added the missing php tags. I apologise if my question is confusing. But I want to do is quite simple. Right now I have place holder names in the html like Kate, John and Max. I want to replace these with the names retrieved from the query. So Kate would be replace by the first result, John by the second, Max by the third. — jamjam
– jamjam, Commented Oct 2, 2011 at 19:08

Johan · Accepted Answer · 2011-10-02 19:03:11Z

1

Try this:

<?php

$people = "SELECT name FROM people";
$people = mysql_query($people) or die(mysql_error());

if(mysql_num_rows($people) > 0){
?>
    <ul>
    <?php
        while ($row_people = mysql_fetch_assoc($people)){ 
    ?>
            <li><?php echo htmlentities($row_people['name']);?></li> 
    <?php
        }      
    ?>
    </ul>

<?php
}

?>

edited Oct 2, 2011 at 19:03

Johan

77.4k28 gold badges204 silver badges346 bronze badges

answered Oct 2, 2011 at 18:40

Vikas Naranje

2,4126 gold badges31 silver badges41 bronze badges

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

11 Comments

jamjam Over a year ago

If am not mistaken this looks like my example above, which I said I could not use as my html would not work with it.

Vikas Naranje Over a year ago

what's your file extension .html or .php if file extension is .php then its gonna work otherwise you can't use php syntax in .html file. All php syntax would be treated as character

Jared Farrish Over a year ago

@jamjam - In all fairness to this answer, your question is not very clear as to what the result is and what the problem is.

jamjam Over a year ago

File extension is not the issue. This works but it produces the same result as my while loop above. I cannot use it as it wraps everything a generic li tag. I want to retain the html structure whilst populating names retrieved from the database.

Jared Farrish Over a year ago

@VikasNaranje - The OP wants to adjust the output of the while loop to match the markup example in the question.

|

deviousdodo · Accepted Answer · 2011-10-02 19:26:13Z

You'll just have to create a renderer for each "type" of user (assuming you have a type property on the user rows) or based on their attributes. For example, let's say you're going to have to filter based on the attributes:

<?php

function render_simple($person) {
    return '<a href="?' . $person['name'] . '">' . $person['name'] . '</a>';
}

function render_with_image($person) {
    return '<a href="?' . $person['name'] . '"><img src="' . $person['image'] . '.jpg"/>' . $person['name'] . '</a>';
}

function render_special($person) {
    return '<a href="?' . $person['name'] . '"><span>' . $person['name'] . '</span></a>';
}

function render_person($person) {
    if ($person['image']) {
        return render_with_image($person);
    }
    if ($person['special']) {
        return render_special($person);
    }
    return render_simple($person);
}

$i = 0;
while ($row_people = mysql_fetch_assoc($people)){ ?>
    <li class="index<?php echo ++$i; ?>">
        <?php echo render_person($person); ?>
    </li>
<?php
}      
?>

This should work, with the exception that instead of class names first, second, etc, you'll now have index1, index2, etc.

Collectives™ on Stack Overflow

Echo results of mysql query with php

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related