How do I loop in PHP and get multiple rows returned from MySQL database?

Question

I have made a log in system which asks for email activation. It all works fine. However, there is no real 'spam' protection. When the user creates an account, it inserts a row with the column 'activated' = 'NO'.

I want to make it so that if the user hasn't activated their account with, let's say, 3 hours, it will delete the row from the MySQL database. I have made a cron job which runs the following every 30 mins.

<?php

ob_start();
include 'global.php';
ob_end_clean();

$check_times = (time() - ('-3 hours'));
$query = "SELECT * FROM users WHERE time<=$check_times and activated='NO'";
$result = mysql_query($query) or trigger_error(mysql_error().$query);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$inactive_user = $row['username'];

echo $inactive_user;
echo "<br>";
echo $check_times;
?>

The 'global.php' is connecting to the database. The above code works but only for one row (IT ONLY RETURNS THE VALUE OF THE FIRST INSTANCE IT COMES ACROSS)

I have looked into 'Do While' and 'Loop Until' and whatever but don't know what to put in the brackets...Do While (//what do I put in here?)

At the moment, the code only echos out the instances but I will change that to delete

Possible duplicate: stackoverflow.com/questions/3137992/…

showdev
– showdev

2013-06-06 17:14:16 +00:00
Commented Jun 6, 2013 at 17:14 — showdev
– showdev, Commented Jun 6, 2013 at 17:14

Perocat · Accepted Answer · 2013-06-06 17:16:46Z

0

You insert all the usernames inside $inactive_user[].

    $inactive_user = array();
while($rows = mysqli_fetch_array($result){
    $inactive_user[] = $rows['username'];
}

And then loop the array and echo every user:

foreach($inactive_user as $us){
    echo $us;
}

As mysql is deprecated, you should use mysqli or PDO

answered Jun 6, 2013 at 17:16

Perocat

1,5217 gold badges25 silver badges50 bronze badges

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

4 Comments

Josh-Mason Over a year ago

Brilliant, works a treat. However, it doesn't seem to like mysqli. Is there a reason for this and how important is it?

Josh-Mason Over a year ago

Sorry...brain freeze. How exactly would I delete them from the table? I was thinking something like: foreach($inactive_user as $us){ mysql_query ("DELETE * FROM users WHERE username = $inactive_user "); }

Josh-Mason Over a year ago

$us is one string and it wouldn't recognise them as different users and $inactive user doesn't work

Perocat Over a year ago

mysql_query("DELETE FROM users WHERE username = '$us'"). Note that you need to set in your TABLE users an UNIQUE INDEX to avoid duplicated usernames. mysql will not be supported in the future. mysqli is the "new updated" mysql. If you want to query with mysqli you have to set your connection with mysqli. E.g. $link = new mysqli($host, $user, $password, $database); php.net/manual/en/mysqli.query.php If my post answered your question, please accept it by click on the check.

jterry · Accepted Answer · 2013-06-06 17:16:44Z

0

while($row = mysql_fetch_array($result)) {
    $inactive_user = $row['username'];
}

Additionally, you should phase out the use of mysql_ functions, and look into mysqli as a replacement - mysql_ is deprecated.

answered Jun 6, 2013 at 17:16

jterry

6,2473 gold badges32 silver badges37 bronze badges

3 Comments

Josh-Mason Over a year ago

I get a lot of errors for line 9 - $result = mysqli_query($query) or trigger_error(mysqli_error().$query);

Josh-Mason Over a year ago

I can't write all the errors but it's expecting at least 2 parameters on line 9 and I get this 'Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given'

jterry Over a year ago

It's not a swapout of functions - there's a bit more to the transition but you absolutely should look into it - perhaps start here.

Collectives™ on Stack Overflow

How do I loop in PHP and get multiple rows returned from MySQL database?

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related