php & mysql

[FunkyBlueStick]

Hi,

I've built a php script that queries a mysql database and presents it. That has worked fine. I wrote another script to add to the sql db but I get the "could not insert $" message, which I defined. I don't think it's a problem with the code but maybe with mysql permissions. I am running the program as root so it probably wouldn't be that but if anyone has had a similar problem then I'd like to know how you got around it!

Here's the offending lines of code if anyone can help!

$sql="insert into numbers values(\"$name\",\"$number\",NOW())";
$result=mysql_db_query("phones","$sql") or die ("<i>Failed to insert</i> <b>$name!</b>");

if ($result)
{
echo ("Successfully added <b>$name</b><br>");
}

Cheers

[This message has been edited by FunkyBlueStick (edited 07 November 2000).]

[Sweede]

first, NEVER EVER EVER EVER RUN MySQL AS ROOT USER.
in your mysql startup script, /usr/bin/safe_mysqld , make sure it starts up as user mysql !

replace your script with this.

$table = "phones";
$database = "db1001a";

mysql_connect("localhost","user","pass");
mysql_select_db($database) or die("cannot select DB");
$sql="insert into $table values(\"$name\",\"$number\",NOW())";
$result = mysql_query($sql) or die("cannot execute query, error: ".mysql_error());


then tell us whats wrong.

[Sweede]

oh yea, and MySQL permissions have NOT A DAMN THING IN THE WORLD to do with Linux permissions system.
root in mysql is NOT root in linux

[nanode]

sweede: if mysql root != system root, then why is this such an issue? Unless there is some means for a web user to submit SQL, I can't see how the DB could be affected.

Besides, don't you use the owner of a DB for the: mysql_connect("localhost","user","pass");

If a web user *could* pass SQL strings via this connect, wouldn't it be done as "user" who has write privledge?

[FunkyBlueStick]

Cheers Swede... http://www.linuxnewbie.org/ubb/smile.gif

I was only running mysql as root to try and discover if there was a problem with the permissions. I did't know how to display errors but I realize now. Still....I tried what you asked and this is the error message I got in return :

Column count doesn't match value count at row 1

Any ideas???

Thanks

------------------
Yours with Peace and Love

Funky..

[Sweede]

loging into mysql as root user is totally seperate from starting mysql as linux root user.
this is what i meant.

BlueStick, show me your table that you created, ( explain table_name)

[FunkyBlueStick]

Swede....It's ok dude, Thanks to your error message I managed to work out what was wrong. It was that I didn't have ID's for each entry in the table. I corrected that and it works.

Thanks alot for your help http://www.linuxnewbie.org/ubb/smile.gif

------------------
Yours with Peace and Love

Funky..

[Sweede]

you can also use something like

INSERT INTO tablename ('field1', 'field2', 'field5) VALUES ('data1','data2','data5');

[FunkyBlueStick]

Swede, I need you help again if you're willing? http://www.linuxnewbie.org/ubb/smile.gif

I've now written a delete script and an edit script to add to the bundle which both work fine (not bad for someone who's never done any programming or scripting before) http://www.linuxnewbie.org/ubb/smile.gif

I have the "date added" displayed in my table but it displays it as yyyy-mm-dd and I'd like to display it as dd-mm-yyyy. Is there anyway to do this?

Please can you enlighten me??? http://www.linuxnewbie.org/ubb/smile.gif




[This message has been edited by FunkyBlueStick (edited 08 November 2000).]

[Sweede]

Originally posted by FunkyBlueStick:

$result = mysql_query("select * from numbers where id=$id") or die ("Could not query, error: ".mysql_error());
the best and cleanest way to do querys is to use variables.

$query ="SELECT * FROM numbers WHERE id =
'".$id."'";

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

proper SQL(NOT PHP) syntax is
SELECT [feilds] FROM [tablename] WHERE [fieldname] = '[data]' ;

Notice the '' around your data.

[FunkyBlueStick]

Thanks Swede...

You obviously saw the message before I had a chance to edit it...I managed to fix the problem I was having so everything is working fine now! Could you refer to my above message about date formats...I promise this'll be the last time I bother you http://www.linuxnewbie.org/ubb/smile.gif

Thanks