need help w/mysqli update

Hi, I'm creating a dropdown from database rows, selecting the row and displaying the same. Everything works as planned except I can't update the one field (lastused)

<?php
echo "<center>";echo date('m/d/y');echo "</center>";
$id="''";
$con=mysqli_connect("localhost","root","cookie","homedb");

// ============== check connection

if(mysqli_errno($con))
{echo "Can't Connect to mySQL:".mysqli_connect_error();}
else
{echo "</br>";}
// ==========This creates the drop down box using records in the table

echo "<select name= 'target'>";
echo '<option value="">'.'---select email account ---'.'</option>';
$query = mysqli_query($con,"SELECT target FROM emailtbl");
$query_display = mysqli_query($con,"SELECT * FROM emailtbl");
while($row=mysqli_fetch_array($query))
{ echo "<option class=highlight value='". $row['target']."'>".$row

['target']
.'</option>';}
echo '</select>';
?>
<input type="submit" name="submit" value="Submit"/>
</form></body></html>

<?php
error_reporting(E_ALL ^ E_NOTICE);
// error_reporting(0);
$con=mysqli_connect("localhost","root","cookie","homedb");
if(mysqli_errno($con))
{echo "Can't Connect to mySQL:".mysqli_connect_error();}
if(isset($_POST['target']))
{
$id = $_POST['id'];
$lastused = $_POST['lastused'];
$name = $_POST['target'];
$fetch="SELECT target, username, password, emailused, lastused,

purpose, saved FROM emailtbl WHERE target = '".$name."'";
$result = mysqli_query($con,$fetch);
if(!$result)
{echo "Error:".(mysqli_error($con));}

// =============================== this displays the table

echo '<table border="1">'.'<tr>'.'<td bgcolor="#FFD47F"

align="center">'. 'email menu'. '</td>'.'</tr>';
echo '<tr>'.'<td>'.'<table border="1">'.'<tr>'.'<td

bgcolor="#ccffff">'.'target'.'</td>'.'<td

bgcolor="#ccffff">'.'username'.'</td>'.'<td bgcolor="#ccffff">'.

'password' .'</td>'.'<td bgcolor="#ccffff">'. 'emailused'. '</td>'.'<td

bgcolor="#FFD47F">'. 'lastused' .'</td>'.'<td bgcolor="#ccffff">'.

'purpose'. '</td>'.'<td bgcolor="#ccffff">'. 'saved' .'</td>'.'</tr>';
// while($data = mysqli_fetch_row($fetch))
while($data=mysqli_fetch_row($result))
{echo ("<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>

$data[3]</td><td>$data[4]</td><td>$data[5]</td><td>$data[6]</td></tr>");}
echo '</table>'.'</td>'.'</tr>'.'</table>';
}
// ---------------------------------------------
// MySqli Update Query
/* $results = $mysqli->query("UPDATE emailtbl SET lastused=curdate()'

WHERE target=$target");
if($results)
{ print 'Success! record updated'; }
else
{ print 'Error : ('. $mysqli->errno .') '. $mysqli->error; } */

?>

 

Double check column name in structure and the type to accept curdate().

 

dates are timestamp

 

1) Might help if you weren't using 1990's markup.

2) Might help us diagnose it if you used the CODE bbcode tag around it.

3) Might help if you weren't using multiple echo to do the job of ONE.

4) Might help be less confusing and probably run faster if you flipped your single and double quote usage and bothered formatting the output.

5) Assuming that's ACTUALLY tabular output, it might help if your table was properly structured. (your number of TD per TR don't match!)

6) Option is not a label any more than placeholder is.

7) Try to use more meaningful names than "query" -- also if you WERE to use vague names that's mysqli, so they should be called "statement" or "stmt", not query.

8) Try to get in the habit of using the object based version -- the procedural are just wrappers that add unnecessary overhead.

9) since OPTION are supposed to ignore recieving styling (no matter what FF thinks) there's no real point in putting the same class on ALL of them. You REALLY want them different put a class on the one that's NOT getting the same value.

10) if the value is the same as the contents of an OPTION, there is NO reason to use the VALUE attribute.

11) is that supposed to be two separate PHP files? I'm assuming so since you closed HTML and BODY. If so, why are you doing a query of all targets and then not doing anything with it?!?

12) if you are going to use mysqli, USE IT. Aka prepared queries -- don't blindly dump variables into query strings like it's still 2005. Prepare and bindparam provide sanitation and a safety buffer. (much less the ability to re-use the statement if desired)

Rewriting that first block...

// going to assume you opened not just the form, but also a FIELDSET

echo '
		<div class="date">', date('m/d/y'), '</div>';

$con = new mysqli('localhost', 'root', 'cookie', 'homedb');

if ($con->connect_error) {

	echo 'Cannot Connect to mySQL: ', $con->connect_error();
	
} else {

	echo '
			<label for="targetEmail">E-Mail Account:</label><br>
			<select name="target">
				<option value="">-- select --</option>';

	$targets = $con->query('SELECT target FROM emailtbl');

	while ($row = $targets->fetch_array(MYSQLI_ASSOC)) echo '
				<option>', $row['target'], '</option>';

	echo '
			</select>
			<input type="submit" name="submit" value="Submit">';
			
}

?>

		</fieldset>
	</form>
</body></html>

Code (markup):

That second block is just a disaster -- I don't see you actually setting 'lastused' so I'm not sure what it contains, where it's coming from, etc, etc... I think your first snippet didn't include enough of the form for us to give you a proper answer. Guessing WILDLY there is little reason for that to be much more than:

$con = new mysqli('localhost', 'root', 'cookie', 'homedb');

if ($con->connect_error) {

	echo 'Cannot Connect to mySQL: ', $con->connect_error();
	
} else {

	if (isset($_POST['target']))	{
		$stmt = $con->prepare('
			SELECT username, password, emailused, lastused, purpose, saved
			FROM emailtbl
			WHERE target = ?
		');
		$stmt->bindValue(1, $_POST['target']);
		$stmt->execute();
		
		if ($row = $stmt->fetch()) {
		
			echo '
				<table class="emailResults">
					<caption>
						Email Activity for ', htmlspecialchars($_POST['target']), '
					</caption>
					<thead>
						<tr>
							<th scope="col">Username</th>
							<th scope="col">Password</th>
							<th scope="col">E-Mail Used</th>
							<th scope="col">Last Used</th>
							<th scope="col">Purpose</th>
							<th scope="col">Saved</th>
						</tr>
					</thead><tbody>';
					
			do {
			
				echo '
						<tr>';
						
				foreach ($row as $value) echo '
							<td>', $value, '</td>';
							
				echo '
						</tr>';
			} while ($row = $stmt->fetch());
			
			echo '
					</tbody>
				</table>';
			
		} else echo '<div class="error">No Results Found</div>';
		
	} else echo '<div class="error">No valid "target" for Query</div>';
	
}

Code (markup):

All the crap I cut out belonging in your EXTERNAL stylesheet.

Your final bit of commented out code with the attempt at SET of lastused leaves me with a bunch of questions -- are you trying to change ALL of them? Just the one of the current target? Are you sure you just want the time and not the whole time and date? I'm thinking you should be using NOW() not CURTIME()

This is REALLY guessing wildly but:

$stmt = $con->prepare('
	UPDATE emailtbl
	SET lastused = NOW()
	WHERE target = ?
');
$stmt->bindParam(1, $_POST['target']);
$stmt->execute();

echo $stmt->error ? '
	<div class="error">
		Lastused update query error: ' . $stmt->error . '
	</div>
' : ( $stmt->affected_rows > 0 ? '
	<div class="success">
		Success! Updated ' . $stmt->affected_rows . ' records.
	</div>
' : '
	<div class="error">
		FAILED! No records had their "last used" time index updated.
	<div>
');

Code (markup):

Though I'm not sure why you'd want to update the last used of ALL the e-mails stored for a particular user to the same time index just because you viewed the list...

You've got a LOT of outdated/outmoded and just bad practices in there, the SAME ones I think I mentioned before and they
continue to bite you in the backside every time you try and do the simplest of things.

Hope this helps.

-- edit -- It's also a REALLY bad practice to EVER build a query that pulls passwords from the database. Things like passwords should have their information flow be mono-directional towards the DB. Likewise pulling the PW should be meaningless since you should be storing them with an "irreversible" hash like SHA256 -- or even better SHA512 or whirlpool.