CODEWITHSUNDEEP
phpmysql
Luke Jackson
Luke Jackson

Reputation: 11

Extracting an ID number from a MySQL Database using PHP

I have a PHP script in the works which is going to end up, checking a database twice, once to see if the user has registered, and once to get a user ID (if someone has registered)

I have that bit working (you can find the code below)

But the value that is returned, isn't what I want. I just want a plain int number, instead I get a string which changes depending on the ID and it is set out like "Resource ID #3" but this isn't useful for me, because I want to use the ID to join two tables together later in the script, but I cant do that if the ID is a string.

Does anyone know how I can get ONLY the number from the ID field?

<?php
$con = mysql_connect("localhost","thecynic_parkadm","parkers");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("thecynic_parkersdb", $con);

//Values to insert into the customer table of database

$firstname = $_POST['firstname'];
$secondname = $_POST['secondname'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$postcode = $_POST['postcode'];
$number1 = $_POST['number1'];
$number2 = $_POST['number2'];
$email = $_POST['email'];

$customerid = mysql_query("SELECT Customer_ID FROM customers WHERE EMail =      '$email'",$con);

//Values to insert into the booking table of database

$time = $_POST['time'];
$date = $_POST['date'];

//End variables

$result= mysql_query("SELECT * FROM customers WHERE EMail = '$email'",$con);
$num_rows = mysql_num_rows($result);

//echo "$num_rows Rows\n";

if($num_rows >= 1)
{ 
echo "$customerid";
//$sql="INSERT INTO booking (Customer_ID, Time, Date) VALUES ('$id', '$time',     '$date')";
}
else
{
$sql="INSERT INTO customers (First_Name, Second_Name, Address1, Address2, Post_Code,   Home_Number, Mobile_Number, EMail)
VALUES ('$firstname', '$secondname', '$address1', '$address2', '$postcode', '$number1',     '$number2', '$email')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
}

mysql_close($con);
?>

Some lines are commented out, I know. But they aren't relevant.

Upvotes: 1

Views: 2898

Answers (5)

onteria_
onteria_

Reputation: 70527

Okay I'm going to answer your question and give you some advice at the same time. What's happening is you're confusing how MySQL calls in PHP work. The process is like this:

  1. Connect to the database
  2. Run the query
  3. Loop through the results until there are no more, acting on each processed row, or grab a single result if there's only one and act upon it.

Now then onwards:

$con = mysql_connect("localhost","user","password");
if (!$con)
{
   die('Could not connect: ' . mysql_error());
}

Okay good here. But if the database fails to connect, you should instead redirect the user to an error page or show them an error on the page they were at. Just dying out creates a bad user experience.

// You can also make your calls to mysql_real_escape_string here
// I do it down below because the calls up to the insert might fail
$firstname = $_POST['firstname'];
$secondname = $_POST['secondname'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$postcode = $_POST['postcode'];
$number1 = $_POST['number1'];
$number2 = $_POST['number2'];
$email = $_POST['email'];

Okay temp variables for readability and easier string expansion, I can deal with that.

$customerid = mysql_query("SELECT Customer_ID FROM customers WHERE EMail =      '$email'",$con);

Some problems here. First off you're subjecting yourself to SQL injection. When you put in user input values, in this case $_POST, you need to escape them using something like mysql_real_escape_string. Why? Read up on SQL Injection and you'll find out. Also since $con is the only active connection, you don't need to specify it.

Next off is the result of mysql_query is either false if the query failed, or a result set to work with. With that in mind you need to make sure the query actually worked:

// I think you meant Email and not EMail by the way
$customer_result = mysql_query("SELECT Customer_ID FROM customers WHERE Email =      '" . mysql_real_escape_string($email) . "'");
if(!$customer_result) {
  // do something here to notify the user that there was an error
  // you can use mysql_error and mysql_errno to see what happened
}

Great, now then if that worked, we need to handle if the customer exists, and if they do grab the customer ID.

if(mysql_num_rows($customer_result) > 0) {
  $customer_info = mysql_fetch_assoc($customer_result);
  $customer_id = $customer_info['Customer_ID'];
}
else {
  $customer_id = 0; // no customer found
}

That's how you fetch the result of a query that returns one result. Now that we've seen if the customer exists or not, and have their ID, we can take the appropriate action:

if($customer_id)
{ 
    // I'm assuming your Customer_ID is numeric, so you don't need to
    // surround it with quotes
    $sql="INSERT INTO booking (Customer_ID, Time, Date) VALUES (
    $customer_id, 
    '" . mysql_real_escape_string($time) . "', 
    '" . mysql_real_escape_string($date) . "'
    )";
    // I give my results individual names so it's easy to see
    // which one is being used
    $booking_result = mysql_query($sql);
    if(!$booking_result) {
      // again handle errors
    }
}
else
{
  $sql="INSERT INTO customers (
   First_Name, Second_Name, Address1, Address2, 
   Post_Code, Home_Number, Mobile_Number, Email)
   VALUES (
   '" . mysql_real_escape_string($firstname) . "', 
   '" . mysql_real_escape_string($secondname) . "', 
   '" . mysql_real_escape_string($address1) . "', 
   '" . mysql_real_escape_string($address2) . "', 
   '" . mysql_real_escape_string($postcode) . "', 
   '" . mysql_real_escape_string($number1) . "',
   '" . mysql_real_escape_string($number2) . "', 
   '" . mysql_real_escape_string($email) . "');
  $customer_insert_result = mysql_query($sql);
  if(!$cusomter_insert_result) {
    // Handle error
  }
}

mysql_close($con);

There might be syntax errors as this is a lto of code for me to test at once, but I hope it explains and shows you a good way of handling code.

Upvotes: 0

Donnie
Donnie

Reputation: 6351

The mysql_query() function is returning an object, so you need to iterate it to get the Customer_ID.

Also, you're running the same query again to see if the record exists. You can eliminate that extra query by initializing the $customerid to 0 and then test to see if it has a value later (which obviously means the record exists).

$customerid = 0;
$result = mysql_query("SELECT Customer_ID FROM customers WHERE EMail = '$email'",$con);
while ($row = mysql_fetch_assoc($result)) {
    $customerid = $row['Customer_ID'];
}

...

if( ! empty($customerid) ) {
    echo $customerid;
} else {
    ...
}

Upvotes: 1

Sourav
Sourav

Reputation: 17520

<?php
$con = mysql_connect("localhost","thecynic_parkadm","parkers");
if (!$con)
  die('Could not connect: ' . mysql_error());

mysql_select_db("thecynic_parkersdb", $con);

$firstname = mysql_real_escape_string($_POST['firstname']);
$secondname = mysql_real_escape_string($_POST['secondname']);
$address1 = mysql_real_escape_string($_POST['address1']);
$address2 = mysql_real_escape_string($_POST['address2']);
$postcode = mysql_real_escape_string($_POST['postcode']);
$number1 = mysql_real_escape_string($_POST['number1']);
$number2 = mysql_real_escape_string($_POST['number2']);
$email = mysql_real_escape_string($_POST['email']);

$result = mysql_fetch_assoc(mysql_query("SELECT Customer_ID FROM customers WHERE EMail =      '$email'",$con));
$customerid = $result['Customer_ID'];


$time = mysql_real_escape_string($_POST['time']);
$date = mysql_real_escape_string($_POST['date']);


$result= mysql_query("SELECT * FROM customers WHERE EMail = '$email'",$con);
$num_rows = mysql_num_rows($result);

//echo "$num_rows Rows\n";

if($num_rows)
{ 
echo $customerid;
//$sql="INSERT INTO booking (Customer_ID, Time, Date) VALUES ('$id', '$time',     '$date')";
}
else
{
 $sql="INSERT INTO customers (First_Name, Second_Name, Address1, Address2, Post_Code,   Home_Number, Mobile_Number, EMail)
 VALUES ('$firstname', '$secondname', '$address1', '$address2', '$postcode', '$number1',     '$number2', '$email')";
 if (!mysql_query($sql,$con))
  die('Error: ' . mysql_error());
}
mysql_close($con);
?>

Upvotes: 1

user680786
user680786

Reputation:

mysql_query returns only pointer to resource, you have to use mysql_fetch_assoc or mysql_fetch_array to fetch data. 

$result = mysql_fetch_assoc(mysql_query("SELECT Customer_ID FROM customers WHERE EMail =      '$email'",$con));
$customerid = $result['Customer_ID'];

read manual: http://php.net/manual/en/function.mysql-query.php and try to use MySQLi or PDO_mysql.

Upvotes: 2

Cfreak
Cfreak

Reputation: 19309

You have to fetch the result. mysql_query just returns a resource: 

$result = mysql_query("SELECT Customer_ID FROM customers WHERE EMail ='$email'",$con);
list($customerid) = mysql_fetch_row($result);

The mysql_fetch_row function returns an array. I use list to automatically take the elements of the array (in this case just one) and populate a variable.

Also you should read up on SQL injection as your code is vulnerable to it.

Upvotes: 2

Related Questions