Jump to content
Larry Ullman's Book Forums

Prepared Statements And Auto-Increment


Recommended Posts

I'm practicing the prepared insert statement in Chapter 13 page 427. Is this how I would I write the prepared statement for a users table in a registration scenerio where the userid is to be assigned by auto-increment?

 

In the bind param I would only have 3 s's and have nothing for the userid and the now()?

$dbc = mysqli_connect ('localhost', 'username', 'password', 'users');

$q = 'INSERT INTO users (userid, first_name, last_name, email, join_date)
 VALUES ('',?,?,?,NOW())';

$stmt = mysqli_prepare($dbc, $q);

mysqli_stmt_bind_param($stmt, 'sss', $first_name, $last_name, $email);

$first_name = strip_tags($_POST['first_name']);
$first_name = strip_tags($_POST['first_name']);
$email = if (filter_var(trim($_POST['email'], FILTER_VALIDATE_EMAIL)))
{
      $email = $_POST['email'];
}
 else
{
      $email = FALSE;
      echo "Error! Not a valid email.";
}

mysqli_stmt_execute($stmt);

if (mysqli_stmt_affected_rows($stmt) ==1)
{
       echo "Entry Successful!";
}
else
{
      echo "Error! Entry Failed!";

      echo '<p>' . mysqli_stmt_error($stmt) . '</p>';
}

mysqli_stmt_close($stmt);
mysqli_close($dbc);
Link to comment
Share on other sites

Yes I did... I copied and pasted the first name intending to change the copied one to last name. Thanks.

 

Would "longtext" be considered a "blob" - "b" in prepared statements?

 

Also,

I'm using a 3 page process - form (enter information), review (go back make changes or continue), add form information into database -> make payment -> update database with Stripe payment details.

 

The information entered into the form on the first page will be set into session variables on the review page (second page). In the prepared statement example on page 427 (chapter 13) it shows that the values need to be assigned to variables after we "bind the variables" - mysqli_stmt_bind_param(). Would I still need to do this if I have already assigned the values to a session variable on page 2? Or would I just do this:

 

My first row in the jobs database table is $jid (before siteid) and it is an auto-increment not null so it doesn't need to be included in this query.

My jobs table:

  `jid` int(11) NOT NULL AUTO_INCREMENT,
  `siteid` varchar(4) NOT NULL,
  `first_name` varchar(25) NOT NULL DEFAULT '',
  `last_name` varchar(35) NOT NULL DEFAULT '',
  `email` varchar(155) NOT NULL DEFAULT '',
  `phone` varchar(25) NOT NULL DEFAULT '',
  `job_title` varchar(155) NOT NULL DEFAULT '',
  `company` varchar(155) NOT NULL DEFAULT '',
  `company_url` varchar(155) DEFAULT NULL,
  `address` varchar(155) DEFAULT NULL,
  `city` varchar(35) NOT NULL,
  `state` varchar(3) NOT NULL,
  `zip_code` varchar(10) DEFAULT NULL,
  `salary` varchar(255) DEFAULT NULL,
  `apply_email` varchar(85) DEFAULT NULL,
  `content` longtext NOT NULL,
  `display` tinyint(2) NOT NULL,
  `payment_status` varchar(15) NOT NULL,
  `amount_paid` varchar(6) NOT NULL,
  `chargeid` varchar(35) NOT NULL,
  `pay_brand` varchar(45) NOT NULL,
  `pay_type` varchar(35) NOT NULL,
  `lastfour` varchar(4) NOT NULL,
  `submitted` date NOT NULL DEFAULT '0000-00-00',
$q = 'INSERT INTO jobs (siteid, first_name, last_name, email, phone, job_title, company, company_url, address, city, state, zip_code, salary, apply_email, content, display, payment_status, amount_paid, chargeid, pay_brand, pay_type, lastfour, submitted)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'','','','','','',NOW())';
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'isssssssssssssbi', $_SESION['siteid'], $_SESION['first_name'], $_SESION['last_name'], $_SESION['email'], $_SESION['phone'], $_SESION['job_title'], $_SESION['company'], $_SESION['company_url'], $_SESION['address'], $_SESION['city'], $_SESION['state'], $_SESION['zip_code'], $_SESION['salary'], $_SESION['apply_email'], $_SESION['content'], $display);

$display = 2;

mysqli_stmt_execute(stmt);

// get auto-incremented jid so as to update this job ad with Stripe payment details and change display status.
$jid = mysqli_insert_id($dbc);

if (mysqli_stmt_affected_rows($stmt) == 1){
    echo '<p>Job ad entered.</p>';
}else{
    echo '<p>Job ad was not entered.' . mysqli_stmt_error($stmt) . '</p>';
}

mysqli_stmt_close($stmt);

Can the mysqli_stmt_error($stmt) error statement work with the config.inc.php file so that if the site is live it won't be displayed publically?

 

This will put the user information into the jobs database. Then I'll process their payment on Stripe and update the table to enter in the - $amount_paid, $chargeid, $pay_brand, $pay_type, $lastfour.

$q = 'UPDATE jobs SET display=?, payment_status=?, amount_paid=?, chargeid=?, pay_brand=?, pay_type=?, lastfour=? WHERE jid=$jid)';
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'issssss', $display, $payment_status, $amount_paid, $chargeid, $pay_brand, $pay_type, $lastfour);

$display = 1;
$chargeid = $charge->id;
$paymentbrand = $charge->card->brand;
$paymenttype = $charge->card->funding;
$lastfour = $charge->card->last4;
$paymentgross = $charge->amount;
$status = "Paid";

mysqli_stmt_execute(stmt);

if (mysqli_stmt_affected_rows($stmt) == 1){
    echo '<p>Job ad updated.</p>';
}else{
    echo '<p>Job ad was not updated.' . mysqli_stmt_error($stmt) . '</p>';
}

mysqli_stmt_close($stmt);

Does this look correct?

Link to comment
Share on other sites

Would "longtext" be considered a "blob" - "b" in prepared statements?

 

I believe that's correct. You can confirm by testing for yourself or checking the PHP manual.

 

The information entered into the form on the first page will be set into session variables on the review page (second page). In the prepared statement example on page 427 (chapter 13) it shows that the values need to be assigned to variables after we "bind the variables" - mysqli_stmt_bind_param(). Would I still need to do this if I have already assigned the values to a session variable on page 2? Or would I just do this:

Yeah, you'd just bind to the session variables. Values don't need to be assigned to variables after their bound, the variables just need to have values before the query is executed.

 

Can the mysqli_stmt_error($stmt) error statement work with the config.inc.php file so that if the site is live it won't be displayed publically?

If you have your own error handler in place and the database error is generated, your error handler should catch it. You can always test this for yourself to confirm.

 

Does this look correct?

Upon cursory glance, it seems like a lot of information in one table, and probably not normalized. If it's theoretically possible for one user to have multiple purchases (multiple job submissions?), you should separate the user table from the jobs/payments table. I wouldn't be surprised if a better design has separate user, job, and payments tables. Walking through normalization would address this.

Link to comment
Share on other sites

 Share

×
×
  • Create New...