Member Avatar for Keidi

Hi all I'm trying to learn php by myself I got as far as creating my first data base and table using the following code:

$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not conect: '.mysql_error());
}
//Create database
if (mysql_query("CREATE DATABASE FATBYTES",$con))
{
echo "Database created";
}
else
{
echo "Error creating database ;".mysql_error();
}
//CREATE TABLE
mysql_select_db("FATBYTES",$con);
$sql = "CREATE TABLE Customers
(
MobileNumber varchar(10),
Sex varchar(8),
FirstName varchar(15),
LastName varchar(15),
Age int,
Email varchar(30)
)";
//Execute Query
mysql_query($sql,$con);
mysql_close($con);

The above works as I can access the database and table through phpMyadmin however the following insert does not work.I get this error"You have a mySQL syntax error...and so on"

$cellno="TEST";
$Title="TEST";
$FirstName="TEST";
$SurName="TEST";
$Age="12";
$Email="TEST";

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

//CREATE TABLE
mysql_select_db("FATBYTES",$con);

//Execute Query



$query = "INSERT INTO Customers (MobileNumber,Sex,FirstName,LastName,Age,Email) VALUES ('$cellno','$Title'','$FirstName','$SurName',8,'$Email')";



if(!(mysql_query($query,$con)))
{print("Couldn't make it happen<br/>");
die(mysql_error());}

mysql_close($con);

Any help highly appreciated.

  • 5 Contributors
  • 5 Replies
  • 213 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by scaiferw

Dani AI

Generated

Nice work getting the database and table created — the immediate INSERT error was a simple SQL-string typo (an extra single-quote) that broke the VALUES list. Good catches from and — when a query fails, first print/log the exact query string and the database error message; that almost always shows where quotes or commas are unbalanced.

Longer term, avoid the old mysql_* API: it was deprecated and removed from current PHP releases, so new code should use MySQLi or PDO and parameterized statements for both compatibility and security. (php.net)

Use prepared statements rather than interpolating values into SQL. The example below shows a minimal MySQLi prepared INSERT and assumes you keep a single connection file (as recommended) to centralize credentials and error handling:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
require_once 'includes/db.php'; // creates $mysqli = new mysqli(...)

$cellno = 'TEST'; $sex = 'M'; $first = 'Test'; $last = 'User';
$age = (int)12; $email = 'test@example.com';

$stmt = $mysqli->prepare(
  "INSERT INTO Customers (MobileNumber,Sex,FirstName,LastName,Age,Email) VALUES (?, ?, ?, ?, ?, ?)"
);
$stmt->bind_param('ssssis', $cellno, $sex, $first, $last, $age, $email);
$stmt->execute();
$stmt->close();
$mysqli->close();

Using MySQLi (or PDO) prepared statements separates code from data and avoids the quoting pitfalls that caused the original error. See the MySQLi and PDO prepared-statement docs for details and error handling examples. (php.net)

For security notes and best practice: parameterized queries are the primary defense against SQL injection — prefer them over manual escaping. When debugging: echo the generated SQL (only on a dev system), check the DB error text, and validate/cast types (e.g., Age to int) before binding. (cheatsheetseries.owasp.org)

Recommended Answers

All 5 Replies

Member Avatar for meffe

in sql query quotes missing in age insertion

this is should work

$query = "INSERT INTO Customers (MobileNumber,Sex,FirstName,LastName,Age,Email) VALUES ('$cellno','$Title'','$FirstName','$SurName','8','$Email')";

Member Avatar for Member #120589

An alternative syntax would be to use SET. I'm forever losing myself in VALUES lists - so you could do this:

$query = "INSERT INTO Customers SET MobileNumber = '$cellno' ,Sex = '$Title',FirstName = '$FirstName',LastName = '$SurName',Age = 8,Email ='$Email'";

is sex = '$Title' right?

Member Avatar for Keidi

@ardav thanks it worked so where was I going wrong?

Member Avatar for SikoSoft

You were going wrong here:

('$cellno','$Title'','$FirstName','....

You accidentally wrote two single quotes side-by-side (right after $Title), which triggered a syntax error in your SQL statement. So there very well may have never been anything wrong with your use at all; it was just a small honest oversight - happens to all of us!

Edited by SikoSoft because: put emphasis on the mistake in the code
Member Avatar for scaiferw

Keidi, welcome to PHP.

At the start of your script, you can simplify it a bit by changing

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

to

$con = mysql_connect("localhost","root","") or die("Could not conect: ".mysql_error());

Here's a further modification that I use to make my error messages display the line where the error occurred. Knowing the line has saved me a lot of grief.

$con = mysql_connect("localhost","root","") or die(__LINE__.": Could not conect: ".mysql_error());

Now here's a little bit mor unsolicited advice, useful if most or all of your work is done in one database.

I always create an include file, in a folder such as /includes/, that has my connect string in it, i.e.:

/includes/dbconnect.inc.php

<?php
  $dbh=mysql_connect ("localhost", "username", "password") 
      or die ('I cannot connect to the database because: ' . mysql_error());
  mysql_select_db ("database") or die('Could not select database: '.mysql_error()); 
?>

Then in the header of each page I simply put an include statement to include that in each page.

include_once("/includes/dbconnect.inc.php");

This way, my code is shorter and if my database connect string ever changes, i.e. I change my password, I only have one place to correct it.

By the way, if you haven't discovered includes yet, you'll want to early on. Includes are a way of saying "Take the contents of this file and stick it right here.". It's a bit like an automated cut and paste.

I always put the structure of the page (the <body> tag and everything before it as well as the menu structure, and then the </body> tag and everything after it) into include files called page-head.inc.php and page-foot.inc.php. The database connection include I put inside the page head include. This greatly simplifies the work.

Hope this is useful!

Rob

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.