MySQL doesn't response or perform the query.

[Berk]

sup
So basically, I'm doing e-commerce site for a habshit retro and I need to get approving works. When you click the tick icon, it should update table like "UPDATE purchase_logs SET approver = "Shop Staff" WHERE id = '".$row['id'] ."'" .. But it does nothing.
My code below:

<?php
 $query = "SELECT * FROM purchase_logs WHERE approver = ''";
$result = mysql_query($query);
echo mysql_error();
echo "<table class='table table-hover'> <thead>
      <tr>
        <th>#</th>
        <th>Username</th>
        <th>Approver</th>
        <th> Approve </th>
      </tr>
    </thead><tbody>"; // start a table tag in the HTML

while($row = mysql_fetch_array($result)){   //Creates a loop to loop through results
$username = $row['username'];
echo "<tr><td>" . $row['id'] . "</td><td>" . $row['username'] . "</td><td>Still needs approve!</td><td><a href='?username=" . $row['username'] . " '>Approve</a></td> </tr></tdbody>";  //$row['index'] the index here is a field name
}if($row <= 0) {
echo "</tbody><tr><td> No more results!</td><td></td><td></td><td></td></tr>";}
$sql = "UPDATE `purchase_logs` SET `approver`='Shop Staff' WHERE (`id`='".$row['id']."')";
echo "</table>"; //Close the table in HTML

echo $id;
 echo $row['id'];
if($_GET['username=$username']) {
  
mysql_query($sql); }
     ?>

 

[Markshall]

Using mysql_ is ill-advised now, the functions are, or will be, deprecated. Try switching to PDO or MySQLi.

Anyway, the problem seems to lie in your if statement.

Try this instead:

if ($_GET['username'] == $username) {
    mysql_query($sql);
}

Also, I know you're grabbing $row['id'] from a database, but try escaping it or at least converting it to an integer (I'm assuming it'd be an integer anyway) for extra security.

For example:

$sql = "UPDATE `purchase_logs` SET `approver`='Shop Staff' WHERE `id`='" . mysql_real_escape_string($row['id']) . "'";

or

$sql = "UPDATE `purchase_logs` SET `approver`='Shop Staff' WHERE `id`='" . ((int)$row['id']) . "'";

 

[Berk]

s

Using mysql_ is ill-advised now, the functions are, or will be, deprecated. Try switching to PDO or MySQLi.

Anyway, the problem seems to lie in your if statement.

Try this instead:

if ($_GET['username'] == $username) {
    mysql_query($sql);
}

Also, I know you're grabbing $row['id'] from a database, but try escaping it or at least converting it to an integer (I'm assuming it'd be an integer anyway) for extra security.

For example:

$sql = "UPDATE `purchase_logs` SET `approver`='Shop Staff' WHERE `id`='" . mysql_real_escape_string($row['id']) . "'";

or

$sql = "UPDATE `purchase_logs` SET `approver`='Shop Staff' WHERE `id`='" . ((int)$row['id']) . "'";

Doesn't seem to work..

 

[Markshall]

I've had to rewrite your code as it was messy and hard to read. I think you were having problems because you were trying to access a variable outside of your while loop.

Anyway, hopefully this works

<?php
if (isset($_GET['id'])) {
    mysql_query("UPDATE `purchase_logs` SET `approver` = 'Shop Staff' WHERE `id` = '" . (mysql_real_escape_string($_GET['id'])) . "'");
}

$query = "SELECT * FROM `purchase_logs` WHERE `approver` = ''";
$result = @mysql_query($query);
if ($result) {
    echo "<table class='table table-hover'><thead><tr><th>#</th><th>Username</th><th>Approver</th><th>Approve</th></tr></thead><tbody>";
  
    if (mysql_num_rows($result) > 0) {
        while ($row = mysql_fetch_array($result)) {
            echo '<tr>
                <td>' . $row['id'] . '</td>
                <td>' . $row['username'] . '</td>
                <td>Still needs approving</td>
                <td><a href="?id=' . $row['id'] . '">Approve</a></td>
            </tr>';
        }
    } else {
        echo '<tr><td colspan="4">No results!</td></tr>';
    }
  
    echo '</tbody></table>';
}

I've put the update query at the top of the script, so it shouldn't still appear in your table after you click the Approve link in a row, it should already be set to 'Shop Staff' before the SELECT query is re-run to find rows that aren't approved.

 

[Berk]

I've had to rewrite your code, I think you were having problems because you were trying to access a variable outside of your while loop.

Anyway, hopefully this works

<?php
if (isset($_GET['id'])) {
    mysql_query("UPDATE `purchase_logs` SET `approver` = 'Shop Staff' WHERE `id` = '" . (mysql_real_escape_string($_GET['id'])) . "'");
}

$query = "SELECT * FROM `purchase_logs` WHERE `approver` = ''";
$result = @mysql_query($query);
if ($result) {
    echo "<table class='table table-hover'><thead><tr><th>#</th><th>Username</th><th>Approver</th><th>Approve</th></tr></thead><tbody>";
   
    if (mysql_num_rows($result) > 0) {
        while ($row = mysql_fetch_array($result)) {
            echo '<tr>
                <td>' . $row['id'] . '</td>
                <td>' . $row['username'] . '</td>
                <td>Still needs approving</td>
                <td><a href="?id=' . $row['id'] . '">Approve</a></td>
            </tr>';
        }
    } else {
        echo '<tr><td colspan="4">No results!</td></tr>';
    }
   
    echo '</tbody></table>';
}

yay! thank you so much duh! love ya

 

[Markshall]

yay! thank you so much duh! love ya

Did that work?

 

[Berk]

yup it did.

Did that work?