PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Fri Dec 13, 2019 4:31 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 23 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Wed May 04, 2016 6:50 am 
Offline
Forum Newbie

Joined: Wed May 04, 2016 6:46 am
Posts: 12
Hi every1! Im new in MySQLI and PHP. Trying to create an editable table, but my code is not working. I'm getting no errors or warnings. It just dont update or add records. Im getting my form with all values but after clicking submit nothing change. The records stil the same.
Here is my code:
Syntax: [ Download ] [ Hide ]
<?php
//connect
include ("db/connect.php");

//create new/edit

function renderForm($nameS = '', $nummerS = '', $dateS='', $stuckS='', $error='',$id=''){
        ?>
        <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
        <html>
        <head>
        <title><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        </head>
        <body>
                <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1>
                <?php if($error !=''){
                        echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>";
                }?>

                <form action="" method="post">
                <div>
                <?php
                        if ($id !=''){?>
                        <input type="hidden" name="id" value="<?php echo $id; ?>"/>
                        <p>ID:<?php echo $id; ?></p>
                        <?php } ?>
                        <strong>Name: </strong> <input type="text" name="name" value="<?php echo $nameS; ?>"/><br/>
                        <strong>Nummer: </strong> <input type="text" name="nummer" value="<?php echo $nummerS; ?>"/><br/>
                        <strong>Date: </strong> <input type="text" name="date" value="<?php echo $dateS; ?>"/><br/>
                        <strong>Stuck: </strong> <input type="text" name="stuck" value="<?php echo $stuckS; ?>"/><br/>
                        <input type="submit" name="submit" value="Submit" />
                </div>
                </form>
        </body>
        </html>

<?php }

/*

EDIT RECORD

*/

// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = htmlentities($_POST['date'], ENT_QUOTES);

//check if empty
if($name == '' || $nummer == '' || $stuck == ''){
        $error = 'ERROR: Please fill in all required fields!';
        renderForm($name, $nummer, $date, $stuck, $error, $id);
}else{
        // if everything is fine, update the record in the database
        if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, cdate=?, stuck =? WHERE id=?")){
                $stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);
                $stmt->execute();
                $stmt->close();
        }else{
                //show error
                echo "ERROR: could not prepare SQL statement.";
        }
        header("location:view.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];

// get the recod from the database
if($stmt = $mysqli->prepare("SELECT * FROM ware WHERE id=?"))
{
        $stmt->bind_param("i",$id);
        $stmt->execute();

        $stmt->bind_result($id,$name, $nummer, $cdate, $stuck);
        $stmt->fetch();

        //show form
        renderForm($name, $nummer, $cdate, $stuck, NULL, $id);
        $stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
header("Location: view.php");
}
}
}

/*

NEW RECORD

*/

// if the 'id' variable is not set in the URL, we must be creating a new record
else
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// get the form data
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = date ("d-m-y");


// check that are both not empty
if ($name == '' || $nummer == '' || $stuck == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($name, $nummer, $cdate, $stuck, $error);
}
else
{
// insert the new record into the database
if ($stmt = $mysqli->prepare("INSERT ware (name, nummer, cdate, stuck) VALUES (?, ?,?,?)"))
{
$stmt->bind_param("ss", $name, $nummer,$cdate, $stuck);
$stmt->execute();
$stmt->close();
}
// show an error if the query has an error
else
{
echo "ERROR: Could not prepare SQL statement.";
}

// redirec the user
header("Location: view.php");
}

}
// if the form hasn't been submitted yet, show the form
else
{
renderForm();
}
}

// close the mysqli connection
$mysqli->close();
?>


Top
 Profile  
 
PostPosted: Wed May 04, 2016 6:54 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Syntax: [ Download ] [ Hide ]
        if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, cdate=?, stuck =? WHERE id=?")){
                $stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);

First thing that jumped out at me. You're trying to define five bound parameters but have only specified types for three. That should be generating an error, but you're not listening for it. Fix that and add some error handling if $mysqli->execute() fails.

_________________


Top
 Profile  
 
PostPosted: Wed May 04, 2016 7:22 am 
Offline
Forum Newbie

Joined: Wed May 04, 2016 6:46 am
Posts: 12


Top
 Profile  
 
PostPosted: Wed May 04, 2016 7:49 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Syntax: [ Download ] [ Hide ]
$stmt->bind_param("ssi",$name,$nummer, $cdate, $stuck,$id);

You still have this to fix. Should probably 'ssssi' (or maybe 'ssisi' -- I don't know what $nummer is, so I'm guessing)

_________________


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:01 am 
Offline
Forum Newbie

Joined: Wed May 04, 2016 6:46 am
Posts: 12
Thank you for fast reply!
I updated that line but stil no results.
Syntax: [ Download ] [ Hide ]
$stmt->bind_param("ssiii",$name,$nummer, $cdate, $stuck,$id);


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:05 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Consider removing the header call so you're not being redirected away before seeing output from your script. Capture the return value of $stmt->execute() and inspect it. Check $mysqli->error(). Those should shed some light on what's happening.

_________________


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:13 am 
Offline
Forum Newbie

Joined: Wed May 04, 2016 6:46 am
Posts: 12
Warning: mysqli::prepare(): (42S22/1054): Unknown column 'cdate' in 'field list' in ...records.php on line 67.
As i said, that $cdate var is giulty!!!
how can i change it?


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:14 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada

_________________


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:16 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada

_________________


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:19 am 
Offline
Forum Newbie

Joined: Wed May 04, 2016 6:46 am
Posts: 12
i reworked it like this.
Syntax: [ Download ] [ Hide ]
$cdate = date ("d-m-y");
...
if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, date=?, stuck =? WHERE id=?"))...
 


so now im getting actuall date and i deleted "C" sign before "date = ?" cause the name of the field is "date" in my table.

now im getting that error
Warning: mysqli_stmt::execute(): (22007/1292): Incorrect datetime value: '0' for column 'date' at row 1 in ...records.php on line 69.

Yeah, thank you for noting that! Im changing it with my edit section!


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:23 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Dates in MySQL are typically YYYY-MM-DD. I recommend storing them as such and doing whatever transforms you need on display. Will make life easier.

_________________


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:38 am 
Offline
Forum Newbie

Joined: Wed May 04, 2016 6:46 am
Posts: 12
i changed it like this
Syntax: [ Download ] [ Hide ]
$cdate = date('Y-m-d H:i:s');


but getting that error:
Incorrect datetime value: '0' for column 'date' at row 1.

any suggestions?


Top
 Profile  
 
PostPosted: Wed May 04, 2016 8:43 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
That line by itself is insufficient context. Looks like something got mixed up somewhere, though, as 0 is being passed to the prepared statement. Please provide your updated code and we can investigate.

_________________


Top
 Profile  
 
PostPosted: Wed May 04, 2016 9:05 am 
Offline
Forum Newbie

Joined: Wed May 04, 2016 6:46 am
Posts: 12
Syntax: [ Download ] [ Hide ]
<?php
//connect
include ("db/connect.php");

//create new/edit

function renderForm($nameS = '', $nummerS = '', $dateS='', $stuckS='', $error='',$id=''){
        ?>
        <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
        <html>
        <head>
        <title><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        </head>
        <body>
                <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1>
                <?php if($error !=''){
                        echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>";
                }?>

                <form action="" method="post">
                <div>
                <?php
                        if ($id !=''){?>
                        <input type="hidden" name="id" value="<?php echo $id; ?>"/>
                        <p>ID:<?php echo $id; ?></p>
                        <?php } ?>
                        <strong>Name: </strong> <input type="text" name="name" value="<?php echo $nameS; ?>"/><br/>
                        <strong>Nummer: </strong> <input type="text" name="nummer" value="<?php echo $nummerS; ?>"/><br/>
                        <strong>Date: </strong> <input type="text" name="date" value="<?php echo $dateS; ?>"/><br/>
                        <strong>Stuck: </strong> <input type="text" name="stuck" value="<?php echo $stuckS; ?>"/><br/>
                        <input type="submit" name="submit" value="Submit" />
                </div>
                </form>
        </body>
        </html>

<?php }

/*

EDIT RECORD

*/

// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = date('Y-m-d H:i:s');

//check if empty
if($name == '' || $nummer == '' || $cdate = '' || $stuck == ''){
        $error = 'ERROR: Please fill in all required fields!';
        renderForm($name, $nummer, $cdate, $stuck, $error, $id);
}else{
        // if everything is fine, update the record in the database
        if($stmt = $mysqli->prepare("UPDATE ware SET name = ?, nummer = ?, date=?, stuck =? WHERE id=?")){
                $stmt->bind_param("ssiii",$name,$nummer, $cdate, $stuck, $id);
                if(!$stmt->execute()) echo $stmt->error;
                /*if($stmt->execute()){
                        // it worked
                        echo "DONE!";
                }else {
                // it didn't
                        echo "ERROR!";
                }*/


                $stmt->close();
        }else{
                //show error
                echo "ERROR: could not prepare SQL statement.";

        }
        //header("location:view.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];

// get the recod from the database
if($stmt = $mysqli->prepare("SELECT * FROM ware WHERE id=?"))
{
        $stmt->bind_param("i",$id);
        $stmt->execute();

        $stmt->bind_result($id,$name, $nummer, $cdate, $stuck);
        $stmt->fetch();

        //show form
        renderForm($name, $nummer, $cdate, $stuck, NULL, $id);
        $stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
//header("Location: view.php");
}
}
}

/*

NEW RECORD

*/

// if the 'id' variable is not set in the URL, we must be creating a new record
else
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// get the form data
$name = htmlentities($_POST['name'], ENT_QUOTES);
$nummer = htmlentities($_POST['nummer'], ENT_QUOTES);
$stuck = htmlentities($_POST['stuck'], ENT_QUOTES);
$cdate = date ("d-m-y");


// check that are both not empty
if ($name == '' || $nummer == '' || $stuck == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($name, $nummer, $cdate, $stuck, $error);
}
else
{
// insert the new record into the database
if ($stmt = $mysqli->prepare("INSERT ware (name, nummer, date, stuck) VALUES (?, ?,?,?)"))
{
$stmt->bind_param("ssii", $name, $nummer,$cdate, $stuck);
$stmt->execute();
$stmt->close();
}
// show an error if the query has an error
else
{
echo "ERROR: Could not prepare SQL statement.";
}

// redirec the user
//header("Location: view.php");
}

}
// if the form hasn't been submitted yet, show the form
else
{
renderForm();
}
}
// close the mysqli connection
$mysqli->close();
?>


error message says:
incorrect datetime value: '0' for column 'date' at row 1 line 69
that is the line with folowing code: (if(!$stmt->execute()) echo $stmt->error;)


Top
 Profile  
 
PostPosted: Wed May 04, 2016 9:17 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Date is a string. That needs to be 'ssisi'

_________________


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 23 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group