Author Topic: mysqli update (Read 6824 times)

12Strings · « **on:** June 03, 2015, 03:19:47 PM »

Hi, the code below creates the result I want (don't know how to post a localhost screenshot). My issue is that I don't know how to link that code, (code 1) to (code 2) which updates the "lastused" file, the current date.
-------------------------------------------------------
(code 1)

Code: [Select]

<!DOCTYPE html><html>
<title>email menu</title>
<head></head>
<BODY><center>
<FORM name=lastused method="post" action=""> 

    <?php
error_reporting(E_ALL ^ E_NOTICE);
// error_reporting(0);
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>

Code: [Select]

           <?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>';
 }
    ?>
    </body></html>

-----------------------------------------------------------
(code 2)

Code: [Select]

<?php
error_reporting(E_ALL ^ E_NOTICE);
$servername = "localhost";$username = "root";$password = "cookie";
$dbname = "homedb";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
   { die("Connection failed: " . mysqli_connect_error()); }
        
      $name = $_POST['target'];
  $sql = "UPDATE emailtbl SET visits = visits + 1, lastused = NOW() WHERE 

target = '".$name."'";
if (mysqli_query($conn, $sql))
     {echo "Record updated successfully";}
else
     {echo "Error updating record: " . mysqli_error($conn);}
?>

-----------------------------------------------------------

camerongray · « **Reply #1 on:** June 03, 2015, 04:19:57 PM »

First things first, I suggest that rather than jumping straight in and building this you go and learn how to properly perform database queries in PHP. At the moment you are putting user input directly into queries which leaves you wide open to SQL Injection Attacks. You need to read up on how to properly escape inputs coming from the user before putting them into a query or use something like PDO to work with prepared statements.

zeroburn · « **Reply #2 on:** June 26, 2015, 05:13:25 PM »

camerongray has a point here. You should not be developing code that leaves user input unfiltered. However, I realize we all start somewhere, just that I would advise you to keep your testing code on a local server, or on a server with a .htaccess entry to restrict access to only your IP address. This is how I started, throwing myself into development and learning what I needed to as the need arose.

Moving on:

Correct me if I am wrong, but it looks like you are trying to essentially timestamp when the records were accessed and increment the "views" each time the script is called.

There are two ways you could do this.

Since you have the "$name = $_POST['target']" therefore requiring a POST request, you could look into a JQuery call in JavaScript to run the code2.php page when code1.php also loads.

You could also use an include or require or require_once and include the code2.php file that way, and find a way of naming the "$name" variable before the include (As variables from your code1.php are run in your includes) and then get rid of the $_POST variable.

Still, the code is vulnerable to SQL Injection attacks and I would keep this on a private server.

I claim not to be an expert so if anyone else has some better solution let us know.

12Strings · « **Reply #3 on:** June 27, 2015, 09:46:12 PM »

Thanks for the response. My past was in data proc., now, with memory probs, it's my pastime, localhost only.
Having written hundreds of docs using mysql I'm attempting to learn mysqli. I intend going to prepared statements, if only for the learning exp.
The following code is my current stab at it.

My object is just to update
fields in a database table. The 3 records in the table are shown in the
subsequent report. All the fields DO have a value and the recur values are 'Y', payrec 'P' and periodic '1 or 6' but nothing gets updated.
No errors indicated.
Any help?

Code: [Select]

 <?php
echo "<center>";echo date('m/d/y');echo "</center>";
$id="''";
 $periodic = '';
 $duedate = '';
$con=mysqli_connect("localhost","user","passwd","mydb");
// ============== check connectiont
    if(mysqli_errno($con))
    {echo "Can't Connect to mySQL:".mysqli_connect_error();}
else
    {echo "connected to database</br>";}
 
 // -----------------------------------------  
    $fetch="SELECT 
acctno, recur, pd, payrec, bname, duedate, datepaid, purpose, amtdue 
FROM testbl 
WHERE recur = 'Y' AND payrec = 'P'";
    $result = mysqli_query($con,$fetch);
    if(!$result)
    {echo "Error:".(mysqli_error($con));}
else
    {echo "database table selected</br>";}

// ----------------------------------------

   // output data of each row
   while($data=mysqli_fetch_row($result))
   {

// ----------------------------------------
          if($periodic == 1)
           { $duedate = date('Y-m-d', strtotime('+4 week')) ."\n"; }
           if($periodic == 6)
           { $duedate = date('Y-m-d', strtotime('+25 week')) ."\n"; }
        $pd = 'P'; $dayslate = 0; 
   
// -------------------------------------------------
         $sql = "UPDATE testbl SET
         duedate = $duedate,
         pd = $pd,      
         dayslate = $dayslate,              
         datepaid = NOW()
        WHERE recur = 'Y' AND payrec = 'P'";

// ------------------------------------------------
 }
header( "refresh:3;url='http://localhost/invoice/autolist.php'");
?>

12Strings · « **Reply #4 on:** July 23, 2015, 03:48:37 PM »

I have checked the validity of the table and in
all 3 records "recur" ='Y', "payrec" ='p', values are in "duedate",
"datepaid" (type is DATE) and "periodic". I'm looping thru the data; var_dump() displays NULL NULL NULL, It seems that my DATEDIFF is flawed.
I've spent considerable time viewing forums, manuals, code types. How about some advice?

Code: [Select]

<?php
error_reporting(E_ALL ^ E_NOTICE);
// error_reporting(0);
$servername = "localhost"; $username = "root";
$password = "cookie"; $dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error)
   { die("Connection failed: " . $conn->connect_error); }
// ==================================================
$sql = "SELECT recur, periodic, pd, payrec, duedate, datepaid,
[b]DATEDIFF(CURDATE(),duedate) AS dayslate[/b]
FROM testbl WHERE recur = 'Y' && payrec = 'P'";
$result = $conn->query($sql);
if ($result->num_rows > 0)
 {
    // output data of each row
    while($row = $result->fetch_assoc()) // ****3 records *****
 {
    // ***************************************************
    var_dump($dayslate); // NULL NULL NULL 
   // ***************************************************
    if ($dayslate > 0)
 { 
    if($dayslate > 120)
    {$pastdue = "PAST DUE";}

    if($periodic == 1)
           { $duedate = date('Y-m-d', strtotime('+4 week')) ."\n"; }
    if($periodic == 6)
           { $duedate = date('Y-m-d', strtotime('+25 week')) ."\n"; }
$pd = 'P'; $daylate = 0;
// ==================================================
$sql = "UPDATE testbl SET
        pd = '$pd',
   duedate = '$duedate',                      
 $datepaid = 'NOW()',
  dayslate = '$dayslate'
 WHERE dayslate = 0";
if ($conn->query($sql) === TRUE)
    { echo "Record updated successfully"; } 
    else
    { echo "Error updating record: " . $conn->error; }

$conn->close(); 
 }
  }
 }
 // header( "refresh:3;url='http://localhost/invoice/autolist.php'");
?>

Computer Hope Forum

News:

Author Topic: mysqli update (Read 6824 times)

12Strings

mysqli update

camerongray

Re: mysqli update

zeroburn

Re: mysqli update

12Strings

Re: mysqli update

12Strings

Re: mysqli update