establish target

1Tsme1941

Member
Hi, hope ur all well. The code below creates a dropdown. I select a record from my database and that
record is displayed showing my username, password, etc. or that record and a link to that target(url).
Thee times visited(visits) and date and time last visited(lastused) but all records are updated?
--------------------------------------------------------------------------------------
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "homedb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
<!DOCTYPE><html><head>
<title>lookup menu</title>
</head>
<body><center><b>
<form name="form" method="post" action="">

<?php
//This creates the drop down box
echo "<select name= 'target'>";
echo '<option value="">' . '--- Select account ---' . '</option>';
$query = mysqli_query($conn, "SELECT target FROM infotbl");
if ($query === false)
{ echo "Something went wrong<br />"; echo mysqli_error($conn); }
else { while ($row = mysqli_fetch_array($query))
{ echo "<option value='" . $row['target'] . "'>" . $row['target'] . '</option>'; }
}
echo '</select>';
?>
<input type="submit" name="submit" value="Submit"/>
</form><center>

<?php

// ==============================================
if (isset($_REQUEST['target']))
{
$target = $_REQUEST['target'];
// ===============================================
$fetch = "SELECT id, target, purpose, user, password, email, visits, lastused
FROM infotbl WHERE target = '" . mysqli_real_escape_string($conn, $target) . "'";
// ===============================================================================

$result = mysqli_query($conn, $fetch);
if (!$result) { echo "Error:" . (mysqli_error($conn)); }

//display the table
echo '<table border="1"><tr><td bgcolor="cyan" align="center">lookup menu</td></tr>
<tr><td>
<table border="1">
<tr>
<td>id</td>
<td bgcolor="#ccffff"> Target </td>
<td bgcolor="violet"> Purpose </td>
<td bgcolor="#ccffff"> User </td>
<td bgcolor="#ccffff">Password </td>
<td bgcolor="#ccffff"> Email </td>
<td bgcolor="#cyan"> Visits </td>
<td bgcolor="#cyan"> lastused</td>
</tr>';

while ($data = mysqli_fetch_row($result))
{

// ==========================================================
$url = "http://$target";
$link = '<a href="' . $url . '">' . $data[0] . '</a>';
// ===========================================================

echo ("<tr><td> $link </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><td>$data[7]</td></tr>");
}
echo '</table>
</td></tr></table>';

// ------------------------------------
$target='target';

$sql = "UPDATE infotbl SET visits=visits+1, lastused=NOW() WHERE target=$target";
if ($conn->query($sql) === TRUE) { echo "Record updated successfully"; }
else { echo "Error updating record: " . $conn->error; }
$conn->close();
// ------------------------------------
}

?>
</body></html>
 

Cromewell

Administrator
Staff member
You are setting $target = 'target' just before your update. I would expect no rows updated. Also, I believe your database column type is a varchar or similar text field so you need to quote it. $sql = "UPDATE infotbl SET visits=visits+1, lastused=NOW() WHERE target='$target'";
 

1Tsme1941

Member
not entirely correct. Every db record is being updated. The "lastused" db column type is datetime as seen in the
updates (2020-03-22 16:38:05)
 

Cromewell

Administrator
Staff member
The "lastused" db column type is datetime as seen in the
I meant the target field that you are using in the where clause is a text type.

The reason I expect no rows to be updated is because your update sql looks like UPDATE infotbl SET visits=visits+1, lastused=NOW() WHERE target=target. So unless all your target field contains 'target' for every row it should be updating none. It's possible there is a mysqlism in there where because of the missing quotes around the string value it updates all rows but that seems like a serious bug they would have found by now.
 

1Tsme1941

Member
sorry, not sure what you're saying. It IS updating all records. It updates none unless coded as posted. ALL record targets begin with www. and target is quoted (WHERE target='$target'";)
 

Cromewell

Administrator
Staff member
// ------------------------------------
$target='target';

$sql = "UPDATE infotbl SET visits=visits+1, lastused=NOW() WHERE target=$target";
if ($conn->query($sql) === TRUE) { echo "Record updated successfully"; }
else { echo "Error updating record: " . $conn->error; }
$conn->close();
// ------------------------------------
This block here, the very first thing you are doing is setting $target = 'target';. That is why I don't think it should update anything. Maybe the code you are running isn't exactly as posted.
 

1Tsme1941

Member
Thanks for your tutorage. You seem to be saying that because of where I'm setting TARGET it shouldn't
update anything - but it does. By exactly posted are you suggesting syntax? I just upgraded(?) to PHP7.
The reason I'm going to forums, I stay in the online manuals but they're so confusing and seem to
assume one already knows.
 

Cromewell

Administrator
Staff member
You seem to be saying that because of where I'm setting TARGET it shouldn't
update anything - but it does
Not where you are setting it, what you are setting it to. It is a static string. I'm sure your version is doing something and has some slight differences, but what you've posted here doesn't.
 

1Tsme1941

Member
You said "You need to quote it.
$sql = "UPDATE infotbl SET visits=visits+1, lastused=NOW() WHERE target='$target'"; "

I changed to: WHERE target='$target'";
and NOW it no longer updates.
 

1Tsme1941

Member
You said "You need to quote it.
$sql = "UPDATE infotbl SET visits=visits+1, lastused=NOW() WHERE target='$target'";

I changed to: WHERE target='$target'";
and NOW it no longer updates.
 

1Tsme1941

Member
'$target='target';
You do not need this line. Without it I would expect it to work.
---------------------------------------------------------------------------
I'm assuming you mean the where portion of the line which when removed, updates all
 

Cromewell

Administrator
Staff member
I mean that line. $target = 'target';
Take it out. Add the where back in, with quotes.

$target is set way up above $target = $_REQUEST['target'];
When you say $target = 'target'; down near the end like that resets it from what was selected to a static string. Which is why it doesn't work.
 

1Tsme1941

Member
finally, thanks so much. man I've danced all around that line. I just misunderstood what you were trying to tell me. you're the bomb!
 
Top