Mysqli update

PHP
1

hi, my projec t is use of mysqli to create menu and update a database record. I get the menu but clicking the submit
doesn’t update. Following is my code:

<!DOCTYPE><html><head><title>Set taxrate</title></head><body><center>
    <form name="form_update" method="post" action="">

 <?php
    $con=mysqli_connect("localhost","root","cookie","homedb");
    //============== check connection
    if(mysqli_errno($con))
    {echo "Can't Connect to mySQL:".mysqli_connect_error();}
    else
    {echo "Connected to mySQL</br>";}
    //=============================
    //This creates the drop down box
    echo "<select name= 'taxrate'>";
    echo '<option value="">'.'---select locale/taxrate ---'.'</option>';
    $query = mysqli_query($con,"SELECT taxrate FROM numbers");
    $query_display = mysqli_query($con,"SELECT * FROM numbers");
    while($row=mysqli_fetch_array($query))
    { echo "<option value='". $row['taxrate']."'>".$row['taxrate']
    .'</option>';}
    echo '</select>';

```html
    ?&gt;
``` 
   <?php
$id='';
$taxrate='';
   $con=mysqli_connect("localhost","root","cookie","homedb");
    if(mysqli_errno($con))
    {echo "Can't Connect to mySQL:".mysqli_connect_error();}
       if(isset($_POST['taxrate']))
 {
    $name = $_POST['taxrate'];
    $fetch="SELECT taxrate FROM numbers WHERE taxrate = '".$name."'";
    $result = mysqli_query($con,$fetch);
    if(!$result)
    {echo "Error:".(mysqli_error($con));}
    //display the table
    echo '<table border="1">'.'<tr>'.'<td align="center">'. 'Tax Menu'. '</td>'.'</tr>';
    echo '<tr>'.'<td>'.'<table border="1">'.'<tr>'.'<td bgcolor="#ccffff">'.'taxrate'.'</td>'.'</tr>';
    //while($data = mysqli_fetch_row($fetch))
    while($data=mysqli_fetch_row($result))
    {echo ("<tr><td>$data[0]</td></tr>");}
    echo '</table>'.'</td>'.'</tr>'.'</table>';

    mysqli_query($con,"UPDATE numbers SET taxrate='$taxrate' WHERE id = '$id'");
    mysqli_close($con);
 }
   ?>     

  </body></html>
2

Hey ckdoublenecks, Been awhile.

Sorry to say, there is so much wrong and unclear with the posted code that I can’t offer a suggestion. You are querying the table numbers to get the taxrate and using this value for both the key and value for your select named taxrate. This in itself is fine thou I don’t like seeing it in the html, especially within the select statement.

But what is not clear is your intention.

Say for example out of your list of tax rates you select 6.5.
You are then making a query for taxrate WHERE taxrate = posted taxrate.
This makes no sense and is not needed. The result of that query would be 6.5
I suppose in way you are using it to control if POST[‘taxrate’] is in that numbers table and if found display the number. Still inefficient and redundant.

Then you go on to UPDATE that taxrate to the same table with an empty $taxrate variable WHERE id = an empty $id variable. I record will not be found or updated.

Maybe if you explain what you are trying to do, someone can help, but it makes little sense as it is.

3

Hi all, after way too much time, research, criticism and even some suggestions this is my code to create a dropdown
from a table, select row, display the row and update one field “lastused” using NOW().

error =

'No rows returned matching id

<?php
$db = new mysqli('localhost', 'root', 'cookie', 'homedb');
if ($db->connect_error) die ('Database connection failed: ' . $db->connect_error );

$emailStmt = $db->prepare('SELECT * FROM emailtbl WHERE id = ? ');
$emailStmt->bind_Param('s', $id); // *****************
$emailStmt->execute();

    if ($email = $emailStmt->fetch())
    { echo 'Last date accessed was ', $email['lastused'];
    $lastStmt = $db->prepare('UPDATE emailtbl SET lastused = NOW() WHERE id = ? ');
    $lastStmt->bind_Param('s', $id); // **********************
    $lastStmt->execute(); }

    else die( 'No rows returned matching id ' . $id ); // *******************
 ?>       

<!DOCTYPE html><html><head><title>email menu</title>
<LINK REL=StyleSheet HREF="lastused.css" TYPE="text/css">
</head><body>
<!-- ------------------------------------------------------------------------ -->

<?php
    $db = new mysqli('localhost', 'root', 'cookie', 'homedb');
    if ($db->connect_error) die ('Database connection failed: ' . $db->connect_error);
    if (isset($_POST['target']))
 {
    $emailStmt = $db->prepare('SELECT target, username, password, emailused, lastused, purpose, saved
        FROM emailtbl WHERE target = ? ');
    $emailStmt->bind_Param('s', $_POST['target']); // *******************
    $emailStmt->execute();

    if ($email = $emailStmt->fetch())
 {
        echo '
            <table class="emailMenu">
               <caption>Email menu</caption>
                <thead>
                    <tr>
                        <th scope="col">username</th>
                        <th scope="col">password</th>
                        <th scope="col">emailused</th>
                        <th scope="col">lastused</th>
                        <th scope="col">purpose</th>
                        <th scope="col">saved</th>
                    </tr>
                </thead><tbody>';

                do
// ------------------------------------------------------
                 { echo '
                    <tr>
                        <td>', $email['target'], '</td>
                       <td>', $email['username'], '</td>
                        <td>', $email['password'], '</td>
                        <td>', $email['emailused'], '</td>
                        <td>', $email['lastused'], '</td>
                        <td>', $email['purpose'], '</td>
                        <td>', $email['saved'], '</td>
                    </tr>'; }
// --------------------------------------------------------
        while ($email = $emailStmt->fetch());
        echo '
                </tbody>
            </table>';

  }
           else echo 'No Matches found!<br />';

  }
          else echo 'You failed to fill out a required field<br />';
  ?>
 [/PHP
```html
]&lt;/body&gt;&lt;/html&gt;
4

Sorry if I criticized your code.
MAN I hate mysqli. It’s such a pain to work with.
OK… what are you trying to do?

I do not see the variable $id defined anywhere.

The only way I could get first query to return a value is to use ->get_result() then fetch… Did I mention I don’t like mysqli? This is what I came up with.

<?php 
$id = "1";
if (isset($id))
{
	$db = new mysqli('localhost', 'root', 'cookie', 'homedb'); 
	if ($db->connect_error) die ('Database connection failed: ' . $db->connect_error );
	
	$emailStmt = $db->prepare('SELECT lastused FROM emailtbl WHERE id = ? ');
	$emailStmt->bind_Param('s', $id); // *****************
	$emailStmt->execute();
   
	$res = $emailStmt->get_result();
	if($row = $res->fetch_assoc()){
		echo 'Last date accessed was ', $row['lastused']; 
		
	    $lastStmt = $db->prepare("UPDATE emailtbl SET lastused = NOW() WHERE id = ? ");
	    $lastStmt->bind_Param('s', $id); // ********************** 
	    $lastStmt->execute(); 
   }else die( 'No rows returned matching id ' . $id ); // *******************  
}
?> 
<!DOCTYPE html><html><head><title>email menu</title>
<LINK REL=StyleSheet HREF="lastused.css" TYPE="text/css">
</head><body>
<!-- ------------------------------------------------------------------------ -->
<?php
if (isset($_POST['target']))
{
	$emailStmt = $db->prepare('SELECT target, username, password, emailused, lastused, purpose, saved
	FROM emailtbl WHERE target = ? ');
	$emailStmt->bind_Param('s', $_POST['target']); // *******************
	$emailStmt->execute();
									
	if ($result = $emailStmt->get_result())
	{
		echo '
		<table class="emailMenu">
		<caption>Email menu</caption>
		<thead>
		<tr>
		<th scope="col">username</th>
		<th scope="col">password</th>
		<th scope="col">emailused</th>
		<th scope="col">lastused</th>
		<th scope="col">purpose</th>
		<th scope="col">saved</th>
		</tr>
		</thead><tbody>';
		
		// ------------------------------------------------------
		while ($email = $result->fetch_assoc()){ 
			echo '
			<tr>
			<td>', $email['target'], '</td>
			<td>', $email['username'], '</td>
			<td>', $email['password'], '</td>
			<td>', $email['emailused'], '</td>
			<td>', $email['lastused'], '</td>
			<td>', $email['purpose'], '</td>
			<td>', $email['saved'], '</td>
			</tr>'; 
		}
		// --------------------------------------------------------
		
		echo '
		</tbody>
		</table>';
	
	}
	else echo 'No Matches found!<br />';

}
else echo 'You failed to fill out a required field<br />';
?>
</body></html>

Now it is very unclear what you wish to do.
You said you wish to create a dropdown yet I see no form or select statement.
I assume the field target might return a number of rows and you wish to select one of these rows to update the lastused field.
Just not seeing the code to make that happen.

Also please note that you can post your whole page. When you break it into these parts it’s harder to read.

5

I normally wouldn’t echo above <html> but set a message to a variable. Just didn’t want to change your code TOO much.

Is “target” returning a bunch of records in that last query and are you wishing to select from That set of records or is there another page POST is coming from?