Updating Database Issue...

[ECP03]

Evening, everyone…

Learned a lot since the last time I posted, but I recently have run into a problem.

The object of the code is to approve forms submitted by individuals.

The difficulty lies in the actual approval of the form itself. There are no syntax errors (at least none I can see) and my SQL is set up to receive updates.

When submitted it works with the appropriate output ‘echo’. However, the database is not updated.

The following is the code which pulls the infromationg information from my database where where ‘approved=0’ and lists it.

[php]<?php

$dbc = mysqli_connect(’...:’,'’,'’,'*’)
or die (‘Error connecting to MySQL server.’);

$query = “SELECT * FROM **** WHERE approved = 0 ORDER BY id ASC”;

$data = mysqli_query ($dbc, $query);

echo ‘

’;

while($row=mysqli_fetch_array($data))
{
echo ‘’;
echo ‘’ . $row[‘id’] . '. Name: ’ . $row[‘first_name’] . ’ ’ . $row[‘last_name’] . ‘’;
echo 'Email address: ’ . $row[‘email’] . ‘’;
echo ‘’;
echo ‘’;
echo ‘’;
}

echo ‘

’;

mysqli_close($dbc);
?>[/php]

This next set is the code in the ‘approve.php’, which is called when the individual clicks the ‘submit’ button in the ‘form’ section.

[php]<?php

if (isset($_POST[‘Approve’]))
{
$dbc = mysqli_connect(’...:’,'’,'’,'*’)
or die (‘Error connecting to MySQL server.’);

$query = "UPDATE **** SET approved=1 WHERE id='$id'";

mysqli_query($dbc, $query)
or die('Error querying database.');

mysqli_close($dbc);

echo 'Abductee approved.';

}

else
{
echo ‘Error approving abductee.’;
}

?>[/php]

The result (again) is the echo output ‘Abductee approved.’

But there is no update to the database itself.

Any help, advice, suggestions, or answer to this problem is greatly appreciated.

-ECP03

[ECP03]

Update to this post:

When removing the WHERE id="$id" , all users on the list with an approved=0, it approves all of them.

Can I use WHERE with UPDATE?

[benanamen]

You can use WHERE with UPDATE.

Nowhere in your code have you assigned a value to $id. Your form is not submitting any values. You at least need to use a hidden field to send the data to approve.php

[polk_farody]

I think your issue is that you’re treating id like a string ‘$id’.
The reason it is not giving you an error is because the statement is perfectly legitimate. Only it is looking for a string value of your id and not an integer.

also use a check like
[php]
if (mysqli_affected_rows($dbc) > 0) {
echo ‘success’;
} else {
echo ‘not as much success’;
}
[/php]
Not 100% on that one but worth a go
** Just add remove the quotes around your $id variable

[benanamen]

[member=73602]polk_farody[/member] , the problem is what I posted. Look at the code in detail and reference what I said about it.

The reason it is not giving you an error is because the statement is perfectly legitimate.

The statement is perfectly NOT legitimate. $id does not exist anywhere. The most obvious reason for no error message is error reporting is not turned on. If it was, he would be getting an undefined index error.

[ECP03]

Thank you Polk and Kevin. Actually, both of your suggestions led me to my answer.

After double checking something in my book late last night, I realized that I didn’t need the anymore, as it was simply about the two buttons and (for which I had an entire other section for my button, not posted, FYI). Figured if I could get one working, the other one wasn’t far behind.

After a good nights rest (and some dream work with a blackboard), the code came together for me. The following is the code I came up.

[php]

Choose to Approve or Remove a user from the database.

<?php

$dbc = mysqli_connect('**.**.***.**:****','****','****','****')
or die ('Error connecting to MySQL server.');

$query = "SELECT * FROM **** ORDER BY id ASC"; 

$data = mysqli_query($dbc, $query);

echo '<table>';

while ($row = mysqli_fetch_array($data))
{
echo ‘’ . $row[‘id’] . '. Name: ’ . $row[‘first_name’] . ’ ’ . $row[‘last_name’] . ‘’;
echo 'Email address: ’ . $row[‘email’] . ‘’;

if ($row['approved'] == '0') 
{
$id = $row['id'];

  echo '<a href="approve.php?id=' . $row['id'] .'">Approve</a>';
  echo ' or ';
  echo '<input type="hidden" value="$id">';
}

$id = $row['id'];

echo '<a href="removemail.php?id=' . $row['id'] .'">Remove</a></br>';
echo '<input type="hidden" value="$id">';  

}

echo ‘’;

mysqli_close($dbc);
?>

[/php]

Keven, your suggestion of the “hidden” field was very helpful in getting me to that point. And Polk, after your reasoning behind trying to pass a string ‘$id’ instead of an integer (which IS what I was trying to do), helped me realize I NEED to specify the integer beforehand. Together, you can notice the $id = $row[‘id’];

I decided to combine both systems and feed off one set of SELECT. With the hidden field in both, I could then use a $_GET to pull that data from the hidden field. The following is only from the Approve.php:

[php]<?php
$dbc = mysqli_connect(’...:’,'’,'’,'*’)
or die (‘Error connecting to MySQL server.’);

$query = "UPDATE **** SET approved=1 WHERE id=" . $_GET['id'];

mysqli_query($dbc, $query)
or die('Error querying database.');

echo 'Abductee approved.<br/>';
echo '<a href="/Lab01/sharedstories.php">see Approved Abductees</a><br>';
echo ' or ';
echo '<a href="admin.php">return to Administrators page</a>';
	
mysqli_close($dbc);

?>[/php]

Polk, you were right, the previous code was TECHNICALLY correct in that it was written correctly, as there were no syntax errors.

I also use DREAMWEAVER, and it does not provide “error reporting” in cases outside of syntax errors. Both of your suggestions or notices helped bring the code to its correct output.

Thanks for helping me. Appreciate it greatly.

[Strider64]

I also use DREAMWEAVER, and it does not provide “error reporting” in cases outside of syntax errors. Both of your suggestions or notices helped bring the code to its correct output.

Thanks for helping me. Appreciate it greatly.

First, Don’t use Dreamweaver as error detection or any IDE for that matter. (You can use them {IDE} as a guide, but I wouldn’t put 100% faith in this method) Actually test your code on either a local server (most common) or a web server.

Second, Did this actually solve your problem or did it not cause any “Errors”? My suspicion is that it actually didn’t solve it, but I could be wrong.

Sorry, I just had to interject into the discussion. ;D

[polk_farody]

[member=73602]polk_farody[/member] , the problem is what I posted. Look at the code in detail and reference what I said about it.

The statement is perfectly NOT legitimate. $id does not exist anywhere. The most obvious reason for no error message is error reporting is not turned on. If it was, he would be getting an undefined index error.

Missed that, assumed the ID was getting set somewhere rookie error.

You should probably check that id has been set before you inject it into your sql aswell

[ECP03]

[member=57087]Strider64[/member],

I use Dreamweaver to do my code and to upload it onto the server space I rent.

As far as as the actual code posted here: no, there were no errors. I believe my issue stemmed from not passing the $id # of the user to the .php file (either approve or remove), and once solving that issue with the and the $_GET, the system works quite well. I’ve tested it to see if there were any bugs, and I have since found none.

When it comes to errors, in my particular case, I find its mostly syntax errors or the absence of necessary code to run. I try to figure it out myself, but I like to get another’s point of view, because maybe they see what I can’t, or am just overlooking.

And no harm interjecting. I encourage any and all ideas to be brought to the table, as it gives a better checklist to tick off as to why the error or problem occurs.

[benanamen]

Improvements you can make are using prepared statements and using FILTER_VALIDATE_INT on your $_GET variable

[benanamen]

You should probably check that id has been set before you inject it into your sql aswell

Absolutely. If that page is called directly it will error.