0

The following php code is meant to drop a table if it exists, create the table, use the table, and then insert a row into the table.

Everything works apart from the insert. I am new to PHP and MYSQL and I have tried many permutations of different types of quotes (singles, doubles, this one: `) but cannot get the data to be inserted into the table.

Can anybody shed some light on what is wrong with this?

$retval = mysqli_query($conn,'INSERT INTO `performance` (manager, program, programid, yearmonth, performance) VALUES ("manager1", "program1","programid1", "199901", "-3.4")');

The php script below gives the output:

Connected successfully

Table dropped successfully.

DB used successfully.

Table created successfully.

Could not insert data.

So everything worked apart from the insert.

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';

$retval = mysql_query('DROP TABLE IF EXISTS `managedfutures`.`performance`') or die(mysql_error());
if(! $retval )
{
  die('Could not drop table  ' . mysql_error());
}
echo "Table dropped successfully.";
echo "<br>";

$retval = mysql_query("USE managedfutures", $conn);
if(! $retval )
{
  die('Could not use DB' . mysql_error());
}
echo "DB used successfully.";
echo "<br>";


$sql = "CREATE TABLE performance( ".
       "performance_id INT NOT NULL AUTO_INCREMENT, ".
       "manager VARCHAR(255) NOT NULL, ".
       "program VARCHAR(255) NOT NULL, ".
       "programid VARCHAR(255) NOT NULL, ".
       "yearmonth VARCHAR(6) NOT NULL, ".
       "performance FLOAT NOT NULL, ".
       "PRIMARY KEY (performance_id )); ";

$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table created successfully.";
echo "<br>";

$retval = mysqli_query($conn,'INSERT INTO `performance` (manager, program, programid, yearmonth, performance) VALUES ("manager1", "program1","programid1", "199901", "-3.4")');
if(! $retval )
{
  die('Could not insert data. ' . mysql_error());
}
echo "Data inserted successfully.";
echo "<br>";

return;

Thanks to Mike W for pointing out that I had mixed mysql and mysqli commands! I am new to php/mysql and did not realise that there was a difference between the two. There was another error also, I was inputting a number as a string in the insert statement. I.e. I wrote "-3.4" instead of just -3.4.

For completeness, here is the fixed version which works.

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    die("Failed to connect to MySQL: " . $mysqli->connect_error);
}
echo 'Connected successfully<br />';

$retval = mysqli_query($mysqli,"DROP TABLE IF EXISTS `performance`");
if(! $retval )
{
  die('Could not drop table  ' . $mysqli->query_error);
}
echo "Table dropped successfully.";
echo "<br>";

$sql = "CREATE TABLE performance( ".
       "performance_id INT NOT NULL AUTO_INCREMENT, ".
       "manager VARCHAR(255) NOT NULL, ".
       "program VARCHAR(255) NOT NULL, ".
       "programid VARCHAR(255) NOT NULL, ".
       "yearmonth VARCHAR(6) NOT NULL, ".
       "performance FLOAT NOT NULL, ".
       "PRIMARY KEY (performance_id )); ";

$retval = mysqli_query($mysqli, $sql);
if(! $retval )
{
  die('Could not create table: ' . $mysqli->query_error);
}
echo "Table created successfully.";
echo "<br>";

$retval = mysqli_query($mysqli, "INSERT INTO `performance` (`manager`, `program`,`programid`, `yearmonth`, `performance`) VALUES ('manager1', 'program1','programid1', '199901', -3.4)");
if(! $retval )
{
  die('Could not insert data. ' . $mysqli->query_error);
}
echo "Data inserted successfully.";
echo "<br>";

return;
Improve this question
3
  • 1
    when asking a question, if six things work and one does not, just post the code for the broken one, thanks Commented Apr 6, 2014 at 5:18
  • Just run the query separately in phpMyAdmin and check whether your query works. Commented Apr 6, 2014 at 5:20
  • @Dagon well actually it appears that I have made a mistake of mixing mysql and mysqli, which I did not know were such different things, and had I not posted all of the things then nobody would have been able to point that out and then inevitably somebody would ask me to post a more complete example with more details of what I am doing. I guess it goes to show: You can't please all of the people all of the time! Commented Apr 6, 2014 at 9:49

2 Answers 2

Reset to default
3

You're mixing mysql_*() and mysqli_*() calls. The two are different and cannot be used together. mysql_*() is deprecated - use only mysqli_*().

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You have used mysql_query throughout your code

$retval = mysql_query( $sql, $conn ); //**You have used mysql_query**
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table created successfully.";
echo "<br>";

Suddenly a mysqli_query is seen (MAGIC !!!).

$retval = mysqli_query($conn,'INSERT INTO `performance` (manager, program, programid, yearmonth, performance) VALUES ("manager1", "program1","programid1", "199901", "-3.4")');
           ^^
      // SUDDENLY you see mysqli_query
Improve this answer

2 Comments

:)... Well feels great to know that that made you laugh... How about giving it a vote-up... ;)
Jokes apart I did strive for it... I tried to run the sql commands and everything was working fine... I racked my brains for quite some time as to what could be the problem... Then I discovered one of the greater sins committed...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.