A few questions on mysql in php...

[BenzZz]

Hi,

Basically I'm creating and implementing a dynamic website as part of my uni final year project but i am a beginner in php so need some help.

First, I'm not entirely sure about how i should go about creating the database, tables and inserting data. I don't mean in terms of the commands as i know quite a bit of sql but i am unsure of where to use the commands. Currently i have all of the in one php file and then require that file once when the website runs- is this a good way?

Each time the site runs it drops the database first, and then does all of the commands again to avoid duplicate values; the problem i can see with this is that when i then insert new data into the database via forms on the site and then exit and rerun the site that data will be gone as the database has been dropped and only the data inserted in the scripts will be there. Is there a simple way to combat this as i am not sure?

Secondly, instead of connecting to mysql using the standard mysql commands e.g. mysql_connect(connection info);, i am using pdos.

As you know from above, i want to create everything within the file including the database, is it possible to create the actual database in pdos? would it just be
$dbh->exec("CREATE DATABASE $db... ?

Also, is there a pdo command equivalent to mysql_select_db($db);? or will the database i would have just created be automatically selected?

All help is appreciated, thanks.

[cossay]

I'm not a PHP wiz but I think I can say something about this.
Making your PHP scripts create databases and tables isn't a bad to me, but just as everything has two sides, a lot of people will tell you why they think is a good idea and a lot will also tell you why they think it is not a good idea. Let's leave this to those who see something wrong about it.
For your first problem, you shouldn't create and drop your databases and tables each time you run your scripts. If you do, the result will just be what you are getting. To overcome that you need to modify your queries. For example,instead of

mysql_query("CREATE DATABASE $database_name");

and then dropping it later, you could write

mysql_query("CREATE DATABASE $database_name IF NOT EXISTS");

After this, you don't need to drop this database and re-create. The database will only be created if it does not exists. Do the same for your table queries and all your data will be intact.
You said you are using pdos for your queries. Have you found any weakness or problems about the php-mysql functions available? If no, why complicate things for yourself?

[BenzZz]

My tutor recommended using pdos as they are more secure.

Is there a conditional INSERT statement available in sql?
So that duplicate values aren't inserted?

[cossay]

Make one of your fields a primary key, then only allow a record to be inserted if the key it contains does not exist.

[godebrown]

here you go
<?php
session_start();
include "opendb.php";
include "confirmlogin.php";
?>

>Primary Key duplicate - that's fine as long as you don't have an autoincrement field. Most tables do, so for this:

INSERT INTO tablename SELECT NULL, 'value2', 'value3', 'value4', 'value5' FROM DUAL
WHERE NOT EXISTS(
    SELECT field1 FROM tablename
    WHERE field2='value2' AND field3='value3' AND field4 = 'value4' AND field5 = 'value5' LIMIT 1
)

This assumes that the first field int he table is an autoincrement field, therefore the NULL: tablename SELECT NULL, The value2, value3 etc will be variables from your form? This should be nice and easy to change the code above.

Example:

$q = "INSERT INTO users SELECT NULL, '$fname', '$sname', '$country', '$hobby' FROM DUAL
WHERE NOT EXISTS(
    SELECT user_id FROM users
    WHERE firstname='$fname' AND surname='$sname' AND country = '$country' AND hobby = '$hobby' LIMIT 1";
)

[BenzZz]

Ah right, thanks for the explanation, makes things clearer :)