Help! Insert Into Problem

Mistah_Roth · Feb 8, 2003

Ok Im having some troubles with PHP and MySQL.

<?php
$username = "ffextreme";
$password = "ffextreme";
$hostname = "localhost";	
$dbh = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to mysql");
print "Connected to MySql<br>";	
if (mysql_query("INSERT INTO ffxitems(name,info,price) values('Potion','Heals Character by 50 Points','100')")) {
  print "Successfully Inserted Item";}
else {
	  print "Failed to Insert Item";
}
mysql_close($dbh);
?>

In the database I have a table called ffxitems and it has 4 columns, first is ID and its auto_increment and then name, then info, then price.

This code always gives me back "Failer to Insert Item" why? It connects the database fine I know that much.

cgraz · Feb 8, 2003

try changing

if (mysql_query("INSERT INTO ffxitems(name,info,price) values('Potion','Heals Character by 50 Points','100')")) { 
print "Successfully Inserted Item";} 
else { 
print "Failed to Insert Item"; 
}

to

$sql = "INSERT INTO ffxitems(name,info,price) VALUES('Potion','Heals Character by 50 Points','100')";
print $sql . "<br>";
$result = mysql_query($sql) or die(mysql_error());

if ($result) {
   print "Successfully Inserted Item"; 
} else { 
   print "Failed to Insert Item"; 
}

That will print out the query string so you can see what it looks like before mysql handles it, then it will run mysql_query on the string and print the error if there is one. These are the best two ways of solving mysql problems. (although the printing isn't really necessary in your case since you don't have any variables in your query string).

Cgraz

Mistah_Roth · Feb 8, 2003

Ok thanks... it did what you said it would, and then it said "Query Empty".... how do I fix that?

cgraz · Feb 8, 2003

can you post the link? I've never gotten this error before.

Cgraz

Mistah_Roth · Feb 8, 2003

http://www.ffextreme.com/itemadder/additems.php

its slightly modified from the one I posted, it has a form which creates the variables used in the query

cgraz · Feb 8, 2003

ahhh! well then that makes a big difference. Your values are all empty, meaning the variables aren't being passed properly into your query.

Change your query to this

$sql = "INSERT INTO ffxitems(name,info,price) VALUES('". $_POST['name'] . "','" . $_POST['info'] . "','" . $_POST['price'] . "')";
print $sql . "<br>";
$result = mysql_query($sql) or die(mysql_error());

if ($result) {
   print "Successfully Inserted Item"; 
} else { 
   print "Failed to Insert Item"; 
}

IF you're on an older version of PHP, replace $_POST['var_name'] with $HTTP_POST_VARS['var_name'], but try this first.

Cgraz

Mistah_Roth · Feb 8, 2003

k... now it says

Connected to MySql
INSERT INTO ffxitems(name,info,price) VALUES('Potion','Heals you','10')
No Database Selected

cgraz · Feb 8, 2003

well you have to connect to your mysql server and select a database

mysql_connect("server", "username", "password");
mysql_select_db("database_name");

// query stuff here

I assumed you had already done that.

Cgraz

Mistah_Roth · Feb 8, 2003

Oops acidently pasted over it, my bad.

It works!! Thank you so much your the best

cgraz · Feb 8, 2003

haha well thank you Hope you learned about printing your query string and using mysql_error()

Someone helped me with that a long time ago on a forum and I've been able to solve all my mysql problems myself using those two methods.

Cgraz

Mistah_Roth · Feb 8, 2003

haha ya it is useful, I got another error on a different script that is associated with that one, and so I did that mysql_error thing and it said the same thing "Query is Empty". The code was

/* construct and run our query */
$query = "SELECT * FROM ffxitems ORDER BY $name";
$result = mysql_query ($query);

/* make sure data was retrieved */
[B]$numrows = mysql_num_rows($result);[/B]
print $numrows . "<br>";
$result = mysql_query($numrows) or die(mysql_error());

if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

Its a part of a script that sorts tables. The error is coming from the area I bolded (I got this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/virtual/site34/fst/var/www/html/items/index.php on line 30

Query was empty

This doesnt fall under the same rules... cuz im not trying to write anything im trying to retrieve it.....

cgraz · Feb 8, 2003

you want to print $query not $num_rows

I'm guessing $name has no value. use $_POST['var_name'] on all your variables from forms. You have register globals set to off.

Change your query to this

$query = "SELECT * FROM ffxitems ORDER BY " . $_POST['name'] . "";

Cgraz

Mistah_Roth · Feb 8, 2003

Ill just post the whole script incase its something else

<?PHP
/* set the allowed order by columns */
$default_sort = 'name';
$allowed_order = array ('name','description','price');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise, 
* set it to what was passed in. */
if (!isset ($_GET['order']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}

$username = "ffextreme";
$password = "ffextreme";
$hostname = "localhost";
$database_name = "ffextreme_com";

/* connect to db */
mysql_connect ("$hostname","$username","$password");
mysql_select_db ("$database_name");

/* construct and run our query */
$query = "SELECT * FROM ffxitems ORDER BY " . $_POST['name'] . ""; 
$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
print $query . "<br>";
$result = mysql_query($numrows) or die(mysql_error());

if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
    /* check if the heading is in our allowed_order
     * array. If it is, hyperlink it so that we can
     * order by this column */
    echo "<TD><b>";
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
    } else {
        echo $heading;
    }                

    echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and 
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";
?>

So I made the changes you asked (I think) and I still get this:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/virtual/site34/fst/var/www/html/items/index.php on line 30
SELECT * FROM ffxitems ORDER BY
Query was empty

cgraz · Feb 8, 2003

not if you want the array to contain the actual words name, description, and price. If you want the array to contain the values from the form, then use $_POST

$_POST is just to get the values from the form.

Instead of calling up a variable from a form like this: $var_name

You'd call it up using $_POST['var_name']

If you want the actual words in the array, then don't use $POST. If you want the values from the form in the array, then use $POST

Cgraz

cgraz · Feb 8, 2003

You've got a few mistakes. Here's your code

/* construct and run our query */ 
$query = "SELECT * FROM ffxitems ORDER BY " . $_POST['name'] . ""; 
$result = mysql_query ($query); 

/* make sure data was retrieved */ 
$numrows = mysql_num_rows($result); 
print $query . "<br>"; 
$result = mysql_query($numrows) or die(mysql_error());

This isn't necessarily a mistake, but i'd change this part of your code to

$result = mysql_query($query) or die(mysql_error());

Then where you have $result = mysql_query($numrows), that's incorrect. You don't run another mysql_query on that. $result contains the results of your query, and mysql_num_rows counts the rows that $result contains. No need to mysql_query again.

And when connecting, I generally leave the quotes out of the mysql_connect and mysql_select_db functions if i'm using variables.

mysql_connect ($hostname,$username,$password);
mysql_select_db ($database_name);

if you have actual values within the functions rather than variables, then you should use quotes.

I don't know if that causes any errors, but just something I noticed.

Cgraz

Mistah_Roth · Feb 8, 2003

so I change

/* construct and run our query */ 
$query = "SELECT * FROM ffxitems ORDER BY " . $_POST['name'] . ""; 
$result = mysql_query ($query); 

/* make sure data was retrieved */ 
$numrows = mysql_num_rows($result); 
print $query . "<br>"; 
$result = mysql_query($numrows) or die(mysql_error());

to

/* construct and run our query */ 
$query = "SELECT * FROM ffxitems ORDER BY " . $_POST['name'] . ""; 
$result = mysql_query($query) or die(mysql_error());

/* make sure data was retrieved */ 
$numrows = mysql_num_rows($result); 
print $query . "<br>";

??? and about the thing at the top, I changed it to

$allowed_order = array ('$_POST['name']','$_POST['description']','$_POST['price']');

and it gives me errors for that

and now its giving me

You have an error in your SQL syntax near '' at line 1

my line one is just <?PHP

cgraz · Feb 8, 2003

yes change your code to that, and the error on the array is because of the single quotes. needs to look like this

$allowed_order = array ('" . $_POST["name"] . "', '" . $_POST["description"] . "', '" . $_POST["price"] . "');

Cgraz

Mistah_Roth · Feb 8, 2003

ok did all that, now the only thing appearing is

You have an error in your SQL syntax near '' at line 1

heres the link if you want it:

http://www.ffextreme.com/items/index.php

cgraz · Feb 8, 2003

Try this

$query = "SELECT * FROM ffxitems ORDER BY " . $_POST['name'];

Cgraz

Mistah_Roth · Feb 8, 2003

no change, ill repost what the entire code is

<?PHP
$default_sort = 'name';
$allowed_order = array ('" . $_POST["name"] . "', '" . $_POST["description"] . "', '" . $_POST["price"] . "');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise, 
* set it to what was passed in. */
if (!isset ($_GET['order']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}

$username = "ffextreme";
$password = "ffextreme";
$hostname = "localhost";
$database_name = "ffextreme_com";

/* connect to db */
mysql_connect ($hostname,$username,$password);
mysql_select_db ($database_name);

/* construct and run our query */ 
$query = "SELECT * FROM ffxitems ORDER BY " . $_POST['name'];
$result = mysql_query($query) or die(mysql_error()); 

/* make sure data was retrieved */ 
$numrows = mysql_num_rows($result); 
print $query . "<br>"; 

if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
    /* check if the heading is in our allowed_order
     * array. If it is, hyperlink it so that we can
     * order by this column */
    echo "<TD><b>";
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
    } else {
        echo $heading;
    }                

    echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and 
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";
?>

Help! Insert Into Problem

MMistah_Roth

Ccgraz

MMistah_Roth

Ccgraz

MMistah_Roth

Ccgraz

MMistah_Roth

Ccgraz

MMistah_Roth

Ccgraz

MMistah_Roth

Ccgraz

MMistah_Roth

Ccgraz

Ccgraz

MMistah_Roth

Ccgraz

MMistah_Roth

Ccgraz

MMistah_Roth