PHP/SQL headwrecking problem...

Darragh29 · 21-02-2007 10:44pm #1

Hi Folks,

I'm a newbie to PHP and SQL, and I'm trying to learn both to admin a database on my company website which is hosted with IrishDomains.com. To start with, I've installed the required value apps through my control panel (PHP MyAdmins, MySQLs and PerlMySql), and I've tried running some simple code in a .php file to do the following:

(1) Create a new table in a database I've set up in PHP My Admins

(2) Insert data into this table.

The weird thing is, I've managed to create the table through the code on the php page, but can't get the data posted into the table after creating the table. I can understand the code below but I'm obviously missing something here!

I've pasted the code below, I know it probably looks a bit rough around the edges but it does actually create a table called "langer" in a database "test", and I've checked that this creation has happened by going into my PHP Admins and I see a new table. For some (probably very simple!) reason that I can't crack, data won't post to the table. I can't move on with this until I get over this obstacle, could someone maybe advise me if I'm missing something simple???

Many thanks in advance for any help!

Dar...

P.S> I downloaded this code from a tutorial I found at: http://www.freewebmasterhelp.com/tutorials/phpmysql/3

<HTML>
<HEAD>
</HEAD>
<BODY>
<?php

$user="root";
$password="banana";
$database="test";

mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";

mysql_query($query);
$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
?>

</BODY>
</HTML>

jayo99 · 21-02-2007 10:52pm

lol..

you only executed the mysql command to create the table..
U need to execute the insert also ;-)

Darragh29 · 22-02-2007 5:51pm

See script above...

mysql_query($query);
$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
?>

As far as I understood, this was all I needed to insert data into the table, which is in the script in my op???

Gosh · 22-02-2007 5:59pm

Darragh29 wrote:

mysql_query($query);
$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
?>

The mysql_query($query) in your script is for the table creation - you need to include another one after the $query= above to run that INSERT

Darragh29 · 22-02-2007 6:02pm

Thanks a mill for this, but which other one, something like...

mysql_query2($query);

or mysql_query($insert);

??? Pardon my ignorance folks, as you can see I'm at the bottom of the learning curve!

Gosh · 22-02-2007 6:04pm

mysql_query is the command and $query is the SQL query string so each time mysql_query($query)

Darragh29 · 22-02-2007 6:25pm

Right, I've 2 mysql_query($query); commands and still no records in my table!?!?!?! This is my latest code, any ideas???

<HTML>
<HEAD>
</HEAD>
<BODY>
<?php

$user="root";
$password="banana";
$database="test";
mysql_query($query)
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="CREATE TABLE langer (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_close();

mysql_query($query);
$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";

?>

</BODY>
</HTML>

Darragh29 · 22-02-2007 6:45pm

I fu*king give up, this is Bo**ox!!!

seamus · 22-02-2007 6:49pm

Heh.

The mysql_query() command has to come after you insert the query into the $query variable. mysql_query($query) executes the query that is in the $query variable, it doesn't prepare it or anything like that.
So...
[php]$query="CREATE TABLE langer (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query)

$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
mysql_query($query);
[/php]

If you read logically through your code, what you're actually doing is:

1. Setting the values of the user, password and host variables.
2. Attempting to execute a MySQL query using the value of the non-existent variable $query.
3. Connecting to the database server.
4. Selecting the database you want to use.
5. Setting the value of the $query variable to the CREATE Table query.
6. Closing the connection to the MySQL database.
7. Attempting to execute a MySQL query using the value of the variable $query, even though you've already closed your connection to the database server.
8. Setting the value of the $query variable to the INSERT statement.

jayo99 · 22-02-2007 10:14pm

lol..

you may wanna brush up on ur logical thinking ;-)

As seamus pointed out.. identify the steps involved & then convert that to code :-)

cython · 22-02-2007 11:34pm

Essentially, what has to be remembered with MySQL and PHP, is that the query has to be constructed, and then executed. Now this can be done in one step by having something like

$result = mysql_query("SELECT * FROM database.tablename;");

This is bad practice, however, as it get's exceedingly messy for anything beyond the simplest of queries.

As a result, the typical approach is to construct the query into a string, and then execute it, like below:

$query = "SELECT * FROM database.tablename;";
$result = mysql_query($query);

To be honest, this stuff should be explained pretty well in any online tutorial, loads of which can be found through Google

Webmonkey · 23-02-2007 12:04am

[php]$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
mysql_query($query); [/php]

Havn't tried it myself but I always thought you had to put the columns before the values.
Eg.
[php]INSERT INTO table (`column1`,`column2`) VALUES ('column1val','column2val')[/php]

Then again I could be wrong, maybe SQL allows you to not specify the columns and automatically do the positioning for you.

v10 · 23-02-2007 12:14am

Darragh29 wrote:

Hi Folks,

I'm a newbie to PHP and SQL, and I'm trying to learn both to admin a database on my company website which is hosted with IrishDomains.com. To start with, I've installed the required value apps through my control panel (PHP MyAdmins, MySQLs and PerlMySql), and I've tried running some simple code in a .php file to do the following:

etc etc etc

Change the php code to :
<?
$user="root";
$password="banana";
$database="test";
mysql_connect(localhost,$user,$password);

mysql_select_db($database) or die( "Unable to select database");

$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);

$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
mysql_query($query);
?>

.
.
.

When you say $query = whatever you're just creating the query

You need to follow that with mysql_query($query); to run the query

cython · 23-02-2007 9:26am

Webmonkey wrote:

[php]$query = "INSERT INTO langer VALUES ('','John','Smith','01234 567890','00112 334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')";
mysql_query($query); [/php]

Havn't tried it myself but I always thought you had to put the columns before the values.
Eg.
[php]INSERT INTO table (`column1`,`column2`) VALUES ('column1val','column2val')[/php]

Then again I could be wrong, maybe SQL allows you to not specify the columns and automatically do the positioning for you.

I think that that's only a necessity if you are only putting values into certain columns. For example, if you have an auto-increment column you wouldn't want to put anything in there, so you would specify all the other columns.

keeffe2001 · 23-02-2007 11:21am

if you do not specify the names of the columns for an insert statement you must supply values for each column in the table and in the right order.

I think when you have an autoincrement column you would have to give all the names of the other columns for an insert.

democrates · 23-02-2007 7:30pm

Darragh29 wrote:

I fu*king give up, this is Bo**ox!!!

We've all been there, I feel your pain posting at 18:45 presumably from work on unpaid overtime, but don't let it break you down you're a hairs breadth from success and this project serves your career interests.

If necessary, manage your bosses expectations, any task only gets easy after the hard part of learning which takes time. Set yourself up a development box, maybe you can install xampp on your normal work pc if it's not already overloaded, or stick a spare pc on your desk and set it up, but using a live public server for learning or developing is a major security faux pas creating a bad risk for your company, and a total pain with the dragged out {modify code, ftp to server, and refresh page} cycle.

You've already set up the table so repeat attempts will obviously fail. If the insert code other boardsies posted still fails, you need a way to find out why, as it is you're left with no clue and you can't develop blind. Fire up phpmyadmin and run the query there, you should get some decent error message that highlights the problem. But by the wings of Pegasus get that development box.

Cake Fiend · 23-02-2007 11:44pm

Sorry to be harsh, but although it's great that you're learning this stuff, someone of your level of knowledge/experience should probably not be admining a live website. Good, secure programming is something that takes a lot of work to master, and you're a long way from it.

If you have no choice in the matter (or don't care), at very least make sure you read a lot about php/sql security, e.g. sql injection attacks and make regular backups.

Darragh29 · 24-02-2007 1:14am

Cake Fiend wrote:

Sorry to be harsh, but although it's great that you're learning this stuff, someone of your level of knowledge/experience should probably not be admining a live website. Good, secure programming is something that takes a lot of work to master, and you're a long way from it.

If you have no choice in the matter (or don't care), at very least make sure you read a lot about php/sql security, e.g. sql injection attacks and make regular backups.

To all who posted, thank you so much for your help with this (especially Seamus, I got it working after your reply! But thanks so much to everyone who replied. I've got over the initial hurdles and now have a db on my website that I can post data to and read data from! Cake Fiend, I totally agree with you! The reason I'm taking this on is for my own business start-up where this functionality is needed on the business website, I wouldn't go near this only its for my own business and the info is not sensitive, its not really useful info unless your a customer, to anyone else, its just useless. None of our competitors are offering this method of providing information back to our customers, so we want to have an edge on that particular front. Thankfully IT/programming/php/sql services is not one of our core products but I have to get this up and running and the cost of getting an IT company to do it is a non runner.

Thanks so much again folks, I'd be lost without the help I got on this thread, I'm on the first rung of the ladder now thanks to ya all!

Dar.

Darragh29 · 24-02-2007 12:06pm

democrates wrote:

We've all been there, I feel your pain posting at 18:45 presumably from work on unpaid overtime, but don't let it break you down you're a hairs breadth from success and this project serves your career interests.

If necessary, manage your bosses expectations, any task only gets easy after the hard part of learning which takes time. Set yourself up a development box, maybe you can install xampp on your normal work pc if it's not already overloaded, or stick a spare pc on your desk and set it up, but using a live public server for learning or developing is a major security faux pas creating a bad risk for your company, and a total pain with the dragged out {modify code, ftp to server, and refresh page} cycle.

You've already set up the table so repeat attempts will obviously fail. If the insert code other boardsies posted still fails, you need a way to find out why, as it is you're left with no clue and you can't develop blind. Fire up phpmyadmin and run the query there, you should get some decent error message that highlights the problem. But by the wings of Pegasus get that development box.

Unfortuantely I'm the (rather poor) boss!

Gosh · 24-02-2007 12:52pm

democrates wrote:

Set yourself up a development box, maybe you can install xampp on your normal work pc if it's not already overloaded, or stick a spare pc on your desk and set it up, but using a live public server for learning or developing is a major security faux pas creating a bad risk for your company, and a total pain with the dragged out {modify code, ftp to server, and refresh page} cycle.

Having a development box that matches your server EXACTLY is a good idea, but installing XAMPP will give you the latest versions of PHP and MySQL - what might work on your development server running XAMPP might not work on your live server. You might be running PHP 5 on XAMPP yet your live server might still be on PHP 4 (something).

If you don't have the same versions something you finally get working uder XAMPP might not work at all when you transfer it to your live server and then the frustration starts again ...

So if you do go down the route of XAMPP or something similar just check you are running the same versions on test and live.

democrates · 25-02-2007 10:45am

Darragh29 wrote:

Unfortuantely I'm the (rather poor) boss!

In the same boat, work any 70 hours you like with no guarantee of income. It'll be worth it in the end.
Gosh makes a good point;

Gosh wrote:

Having a development box that matches your server EXACTLY is a good idea, but installing XAMPP will give you the latest versions of PHP and MySQL - what might work on your development server running XAMPP might not work on your live server. You might be running PHP 5 on XAMPP yet your live server might still be on PHP 4 (something).

If you don't have the same versions something you finally get working uder XAMPP might not work at all when you transfer it to your live server and then the frustration starts again ...

So if you do go down the route of XAMPP or something similar just check you are running the same versions on test and live.

Spot on, even output from mysqldump may have to be edited to transfer between mysql versions quite apart from php differences on sessions etc.

A clatter of XAMPP versions are available at sourceforge - http://sourceforge.net/project/showfiles.php?group_id=61776 scroll down past the first downloads table to the full list and check out the release notes to see which matches your live server versions, however if no bundle of versions match then it's better to forget xampp and install the seperate versions of apache, mod_php, and mysql.

To find out your online server settings make a file info.php containing the following:[PHP]<?
phpinfo();
?>
[/PHP] upload to the server and call from your browser, save the resulting page on your pc and then remove the info.php file from the server to avoid leaving a security risk.

This will have the version information you need for php and apache.
phpmyadmin will reveal your mysql server* version, not to be confused with the 'Client API version' of mysql reported by phpinfo();

Match these with the versions you install on your dev box and pay particular attention to php.ini settings for safe_mode and register_globals as these have implications for coding.

The amp references are:
http://httpd.apache.org/docs/
http://dev.mysql.com/doc/
http://www.zend.com/manual/index.php

Enjoy!

PHP/SQL headwrecking problem...

Comments