INSERT fails from PHP, but works in myslqAdmin

[alberto_zurita]

Hi there,

I can retrieve information from mySQL using PHP and Apache Server using SELECT. But, whenever I try to insert a record on mySQL from PHP(using Dreamweaver) it fails. However, I can retrieve records from the tables on the database, but as I said before it fails to insert them. No, errors or anything.

I know the mysql query runs and it is correct because it inserts the record just using mysqlAdmin byitself.
I am new to PHP/MYSQL.

Please help me.


Thanks in advance!



Alberto

[khendar]

In your PHP code - echo the query you are trying to execute before running it. This way you get a print out of exactly what its trying to do. Copy and paste this into myadmin and see if it still works.

[txmedic03]

Your first mistake was using a HTML editor to do PHP scripting. Now if you will post the exact query here maybe you will get a solution to your problem.

[alberto_zurita]

Thanks for your quick response.

The query works perfect in mysqlAdmin(it inserts a record). Here goes the query which is very simple. It works, but somehow it won't insert any record on my database.


mysql_connect( 'localhost', 'Oscar', 'musico' )
or die ( 'Unable to connect to server.' );

// Select database on MySQL server

mysql_select_db( 'musicians' )
or die ( 'Unable to select database.' );


$mysql = "SELECT companysys2.company_id, company_name, expiration_date FROM companysys1 LEFT JOIN companysys2 USING(company_id)";


//now SELECT works fine, it extracts the records from the database correctly, but if I go:


"INSERT INTO companysys2 VALUES(8, 9, 'zuritaproperty')";

It does nothing




//the table companysys2 has 3 fields:

1)company_id INT(11)
2)wholesale_id INT(11)
3)company_name VARCHAR(32)



Thanks again!


Alberto




[!--quoteo(post=361464:date=Apr 3 2006, 11:07 PM:name=khendar)--][div class=\'quotetop\']QUOTE(khendar @ Apr 3 2006, 11:07 PM) [snapback]361464[/snapback][/div][div class=\'quotemain\'][!--quotec--]
In your PHP code - echo the query you are trying to execute before running it. This way you get a print out of exactly what its trying to do. Copy and paste this into myadmin and see if it still works.
[/quote]


By the way Khendar, I did echoed the query, and pasted into mysqlAdmin. It works there (i.e., inserts the record)


Thanks!

[khendar]

OK Try adding:

or die (mysql_error()) to the end of your query execution command. See if it throws any errors.

[alberto_zurita]

[!--quoteo(post=361464:date=Apr 3 2006, 11:07 PM:name=khendar)--][div class=\'quotetop\']QUOTE(khendar @ Apr 3 2006, 11:07 PM) [snapback]361464[/snapback][/div][div class=\'quotemain\'][!--quotec--]
In your PHP code - echo the query you are trying to execute before running it. This way you get a print out of exactly what its trying to do. Copy and paste this into myadmin and see if it still works.
[/quote]


I added: or die (mysql_error()) at the end of the script. and also tried:

$link = "INSERT INTO companysys2 VALUES(8, 9, 'zuritaproperty')";
echo mysql_errno($link) . ": " . mysql_error($link). "\n";


They don't give any errors, which means the code runs smoothly. However it does not insert the record in the database. I wonder why SELECT extracts the record, but INSERT is not doing its job?

Any extra tips?


Thanks!



Alberto

[khendar]

[code]
$link = "INSERT INTO companysys2 VALUES(8, 9, 'zuritaproperty')";
echo mysql_errno($link) . ": " . mysql_error($link). "\n";
[/code]

Is that all the code ? That wont do anything because you are not running a mysql_query() and $link is not a database connection resource.

Where are you executing the query ?

You need something like:

[code]

$query = "INSERT INTO companysys2 VALUES(8, 9, 'zuritaproperty')";

$result = mysql_query($query);
if(mysql_error())
    echo mysql_errno() . ": " . mysql_error(). "\n";
[/code]

[alberto_zurita]

[!--quoteo(post=361803:date=Apr 4 2006, 09:11 PM:name=khendar)--][div class=\'quotetop\']QUOTE(khendar @ Apr 4 2006, 09:11 PM) [snapback]361803[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]
$link = "INSERT INTO companysys2 VALUES(8, 9, 'zuritaproperty')";
echo mysql_errno($link) . ": " . mysql_error($link). "\n";
[/code]

Is that all the code ? That wont do anything because you are not running a mysql_query() and $link is not a database connection resource.

Where are you executing the query ?

You need something like:

[code]

$query = "INSERT INTO companysys2 VALUES(8, 9, 'zuritaproperty')";

$result = mysql_query($query);
if(mysql_error())
    echo mysql_errno() . ": " . mysql_error(). "\n";
[/code]
[/quote]


It works!!

Thanks Khendar. You are awesome!

You are right. I didn't put the query inside the query function. That was the whole problem. Somehow, I thought the result query was only necessary to display the records. But since I was only inserting them I thought it was not necessary to do a query for insert. Now, I understand more this. Basically the $query variable gets a pointer from the query expression. Then this pointer is used to access the database, based on Insert and Select.

However, I tested with the variables $query, $mysql, $link, and they all seem to work.



My respects to you!

[txmedic03]

I don't think you fully grasp the concept yet, but hopefully you are at least headed in the right general direction.

[alberto_zurita]

txmedic03 your answers don't help at all.


Can you help me to fully grasp the concept then?

[txmedic03]

The reason the query, didn't work is as you have no realized because you didn't use mysql_query(), but do you understand what you actually did? What you did was define a variable the contents of which was a string. That string was a SQL statement, but you must actually do something with the statement hence the need for the mysql_query(). Saying $query = "SELECT * FROM table"; is no different than saying $myname = "James";.

Now for the $link, $mysql and $query...those are just more variables and can be anything. You can use $myreallypoorlynamedvariable = mysql_connect();. It now holds the reference to the open database server connection. This works just the same as if you had used $link or $conn.

[a href=\"http://www.php.net/manual/en/function.mysql-connect.php\" target=\"_blank\"]mysql_connect()[/a]
[a href=\"http://www.php.net/manual/en/function.mysql-query.php\" target=\"_blank\"]mysql_query()[/a]

What I meant was for you to go and read the mysql_ functions you needed to get a more complete understanding rather than someone telling you it was wrong and this is the correct way, but you not knowing why. If you don't fully understand something you can't expect to use it to its full potential.

My comments aren't always meant to give you the answer on a silver platter.

[alberto_zurita]

I did realized the query didn't worked because I didn't put the reference pointer inside the query function mysql_query($anyvariable). I did get confused about using $query, $sql, $mysql, $link. Khendar said I could only use $query because that is the only database connection resource. But now I realize as you said you can use any variable!

Thanks to both of you for your help!


Regards,

Alberto