multiple sql queries

  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

Ok, my code is like this

PHP Code: [ Select ]
 
$sql = "CREATE TABLE `table2` (
 
`id` INT( 8 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
 
`image` VARCHAR( 225 ) NOT NULL ,
 
`cat` VARCHAR( 225 ) NOT NULL ,
 
`subcat` VARCHAR( 225 ) NOT NULL ,
 
`description` VARCHAR( 225 ) NOT NULL ,
 
`date` VARCHAR( 225 ) NOT NULL ,
 
PRIMARY KEY ( `id` )
 
);";
 
echo 'Creating table: \'gs_photo\'....';
 
mysql_query( $sql, $conn );
 
 
 
 
 
$sql = "CREATE TABLE `table1` (
 
`id` INT( 9 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
 
`cat` VARCHAR( 225 ) NOT NULL ,
 
`catdesc` VARCHAR( 225 ) NOT NULL ,
 
`subcat` VARCHAR( 225 ) NOT NULL ,
 
`subcatdesc` VARCHAR( 225 ) NOT NULL ,
 
PRIMARY KEY ( `id` )
 
);";
 
echo 'Creating table: \'gs_photogallery\'....';
 
mysql_query( $sql, $conn );
 
 
  1.  
  2. $sql = "CREATE TABLE `table2` (
  3.  
  4. `id` INT( 8 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
  5.  
  6. `image` VARCHAR( 225 ) NOT NULL ,
  7.  
  8. `cat` VARCHAR( 225 ) NOT NULL ,
  9.  
  10. `subcat` VARCHAR( 225 ) NOT NULL ,
  11.  
  12. `description` VARCHAR( 225 ) NOT NULL ,
  13.  
  14. `date` VARCHAR( 225 ) NOT NULL ,
  15.  
  16. PRIMARY KEY ( `id` )
  17.  
  18. );";
  19.  
  20. echo 'Creating table: \'gs_photo\'....';
  21.  
  22. mysql_query( $sql, $conn );
  23.  
  24.  
  25.  
  26.  
  27.  
  28. $sql = "CREATE TABLE `table1` (
  29.  
  30. `id` INT( 9 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
  31.  
  32. `cat` VARCHAR( 225 ) NOT NULL ,
  33.  
  34. `catdesc` VARCHAR( 225 ) NOT NULL ,
  35.  
  36. `subcat` VARCHAR( 225 ) NOT NULL ,
  37.  
  38. `subcatdesc` VARCHAR( 225 ) NOT NULL ,
  39.  
  40. PRIMARY KEY ( `id` )
  41.  
  42. );";
  43.  
  44. echo 'Creating table: \'gs_photogallery\'....';
  45.  
  46. mysql_query( $sql, $conn );
  47.  
  48.  


the above works perfectly, tested it.

then i also add:

PHP Code: [ Select ]
 
//legends
 
$ip = $_SERVER["REMOTE_ADDR"];
 
$user = $_POST["user"] ;
 
$pass = md5($_POST['pass']);
 
 
 
//insert
 
$sql = "INSERT INTO `gs_admin` ('ip', 'lastvisit', 'user', 'pass') VALUES ('$ip', 'NOW()', '$user', '$pass')";
 
echo 'Creating admin account';
 
mysql_query( $sql, $conn );
 
 
  1.  
  2. //legends
  3.  
  4. $ip = $_SERVER["REMOTE_ADDR"];
  5.  
  6. $user = $_POST["user"] ;
  7.  
  8. $pass = md5($_POST['pass']);
  9.  
  10.  
  11.  
  12. //insert
  13.  
  14. $sql = "INSERT INTO `gs_admin` ('ip', 'lastvisit', 'user', 'pass') VALUES ('$ip', 'NOW()', '$user', '$pass')";
  15.  
  16. echo 'Creating admin account';
  17.  
  18. mysql_query( $sql, $conn );
  19.  
  20.  


and of course there is a form with the inputs "user" and "pass". But it just dont work.

so altogether its:

PHP Code: [ Select ]
 
$sql = "CREATE TABLE `table2` (
 
`id` INT( 8 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
 
`image` VARCHAR( 225 ) NOT NULL ,
 
`cat` VARCHAR( 225 ) NOT NULL ,
 
`subcat` VARCHAR( 225 ) NOT NULL ,
 
`description` VARCHAR( 225 ) NOT NULL ,
 
`date` VARCHAR( 225 ) NOT NULL ,
 
PRIMARY KEY ( `id` )
 
);";
 
echo 'Creating table: \'gs_photo\'....';
 
mysql_query( $sql, $conn );
 
 
 
 
 
$sql = "CREATE TABLE `table1` (
 
`id` INT( 9 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
 
`cat` VARCHAR( 225 ) NOT NULL ,
 
`catdesc` VARCHAR( 225 ) NOT NULL ,
 
`subcat` VARCHAR( 225 ) NOT NULL ,
 
`subcatdesc` VARCHAR( 225 ) NOT NULL ,
 
PRIMARY KEY ( `id` )
 
);";
 
echo 'Creating table: \'gs_photogallery\'....';
 
mysql_query( $sql, $conn );
 
 
 
//legends
 
$ip = $_SERVER["REMOTE_ADDR"];
 
$user = $_POST["user"] ;
 
$pass = md5($_POST['pass']);
 
 
 
//insert
 
$sql = "INSERT INTO `table5` ('ip', 'lastvisit', 'user', 'pass') VALUES ('$ip', 'NOW()', '$user', '$pass')";
 
echo 'Creating admin account';
 
mysql_query( $sql, $conn );
 
 
  1.  
  2. $sql = "CREATE TABLE `table2` (
  3.  
  4. `id` INT( 8 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
  5.  
  6. `image` VARCHAR( 225 ) NOT NULL ,
  7.  
  8. `cat` VARCHAR( 225 ) NOT NULL ,
  9.  
  10. `subcat` VARCHAR( 225 ) NOT NULL ,
  11.  
  12. `description` VARCHAR( 225 ) NOT NULL ,
  13.  
  14. `date` VARCHAR( 225 ) NOT NULL ,
  15.  
  16. PRIMARY KEY ( `id` )
  17.  
  18. );";
  19.  
  20. echo 'Creating table: \'gs_photo\'....';
  21.  
  22. mysql_query( $sql, $conn );
  23.  
  24.  
  25.  
  26.  
  27.  
  28. $sql = "CREATE TABLE `table1` (
  29.  
  30. `id` INT( 9 ) DEFAULT '0' NOT NULL AUTO_INCREMENT ,
  31.  
  32. `cat` VARCHAR( 225 ) NOT NULL ,
  33.  
  34. `catdesc` VARCHAR( 225 ) NOT NULL ,
  35.  
  36. `subcat` VARCHAR( 225 ) NOT NULL ,
  37.  
  38. `subcatdesc` VARCHAR( 225 ) NOT NULL ,
  39.  
  40. PRIMARY KEY ( `id` )
  41.  
  42. );";
  43.  
  44. echo 'Creating table: \'gs_photogallery\'....';
  45.  
  46. mysql_query( $sql, $conn );
  47.  
  48.  
  49.  
  50. //legends
  51.  
  52. $ip = $_SERVER["REMOTE_ADDR"];
  53.  
  54. $user = $_POST["user"] ;
  55.  
  56. $pass = md5($_POST['pass']);
  57.  
  58.  
  59.  
  60. //insert
  61.  
  62. $sql = "INSERT INTO `table5` ('ip', 'lastvisit', 'user', 'pass') VALUES ('$ip', 'NOW()', '$user', '$pass')";
  63.  
  64. echo 'Creating admin account';
  65.  
  66. mysql_query( $sql, $conn );
  67.  
  68.  


i used table 5 as an example, as if it has fields like ip lastvisit and user etc etc

and nothing shows.... i even tried adding "$query" instead of "$sql" on the INSERT query.

and yes, before all this the database has been connected.
  • Mr. Wiggles
  • Graduate
  • Graduate
  • Mr. Wiggles
  • Posts: 160
  • Loc: Brizzle

Post 3+ Months Ago

I tihnk you should change the $sqls into $sql1 and $sql2. Make sure you know which $sql you want your code to execute aswell. This might be right.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

but if i just have $sql, it works perfectly anyway...its just when i want to insert something in a table thats been created. Thats when i get the prob
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Code: [ Select ]
$sql = "INSERT INTO `table5` ('ip', 'lastvisit', 'user', 'pass') VALUES ('$ip', NOW(), '$user', '$pass')";


I think that might solve the problem. When calling NOW() you don't have to wrap it in single quotes.

I got a feeling that if the field was set to date time then it would have rejected that as a value and not inserted it.

Just a hunch
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

i tried that too, and thought the same.

It didnt work...
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Do you get any error messages?
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

nothing shows. however if i just create tables it will show:

creating gs_photogallery etc etc...

if i then add the "INSERT" code too, the page is blank.
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

here's what php.net has to say:
http://us4.php.net/manual/en/function.mysql-query.php

There is at least one posting about multiple queries.

Where does it fail, exactly? Does it create the tables, but not insert anything?

You're not actually printing anything other than the creating admin account string, after all...

Also, maybe try using string concats... Now() create a date variable, not a string (I think...) and is, either way, intrepreted by mySQL not PHP, so I don't think it should be in quotes.

PHP Code: [ Select ]
$sql = "INSERT INTO `table5` ('ip', 'lastvisit', 'user', 'pass') VALUES ('".$ip."', now(), '".$user."', '".$pass."')";


EDIT: Also, (given your previous post, that I didn't see...) double check to make sure you're user has the proper permissions to create tables.

.c
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

thats the thing, i dont know where its failing.

All i know is that, if i want to insert data straight after creating tables... it just does not happen.
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Are the tables being created when you run the script?

Drop the tables, and start from scratch (obvioulsy, use a dev database if this is production...)

.c
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

Yep i been doing this script slowly. First i tested creating one table.

That worked.

Then multiple tables... they worked.

But then when i want to create multiple tables and then insert information straight after... its like a no go area.
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Ok, so when you run it, it will create both tables, print creating table blah after each, but then won't insert the data to gs_admin?

Try using the SQL I gave above and let me know if that works.

.c
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

ok i will, one sec... and what you said is absolutely correct
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

No no no no - it can't be that simple can it?

Try this
Code: [ Select ]
$sql = "INSERT INTO `table5` (`ip`, `lastvisit`, `user`, `pass`) VALUES ('".$ip."', now(), '".$user."', '".$pass."')";

Will explain if it works
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

nah dont work, im now going to try rabid dogs solution.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

rabid, yours didnt work either. :O
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Code: [ Select ]
$sql = "INSERT INTO table5 (ip, lastvisit, user, pass) VALUES ('".$ip."', now(), '".$user."', '".$pass."')";


remove all the single quotes etc from the statement like I have above - btw, do you have a table structured like this?
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

do you mean as in that order?

then yes.....
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

No luck.... rabid
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Did taking the single quotes out work?
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

What is the table structure? I got a feeling about something but I need to see the table structrue that you are trying to insert this into to verify it.

Change the table name to something else if you are worried about security.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

nah, no luck.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

PHP Code: [ Select ]
 
// Insert tables in to the sql database.
 
$sql = "CREATE TABLE `tablename` (
 
`ip` CHAR( 15 ) NOT NULL ,
 
`lastvisit` VARCHAR( 30 ) NOT NULL ,
 
`user` VARCHAR( 20 ) NOT NULL ,
 
`pass` VARCHAR( 20 ) NOT NULL
 
);";
 
 
 
echo 'Creating table: \'tablename\'....';
 
mysql_query( $sql, $conn );
 
 
  1.  
  2. // Insert tables in to the sql database.
  3.  
  4. $sql = "CREATE TABLE `tablename` (
  5.  
  6. `ip` CHAR( 15 ) NOT NULL ,
  7.  
  8. `lastvisit` VARCHAR( 30 ) NOT NULL ,
  9.  
  10. `user` VARCHAR( 20 ) NOT NULL ,
  11.  
  12. `pass` VARCHAR( 20 ) NOT NULL
  13.  
  14. );";
  15.  
  16.  
  17.  
  18. echo 'Creating table: \'tablename\'....';
  19.  
  20. mysql_query( $sql, $conn );
  21.  
  22.  



so...

| ip | lastvisit | user | pass |
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

i have now tried just to insert the data....

by using this:

PHP Code: [ Select ]
 
<?PHP
 
 
 
   $location = $_POST["sqlhost"];
 
   $username = $_POST["sqluser"];
 
   $password = $_POST["sqlpass"];
 
   $database = $_POST["sqldbase"];
 
   $conn = mysql_connect("$location","$username","$password");
 
 
 
   if (!$conn) die ("Could not connect MySQL");
 
   mysql_select_db($database,$conn) or die ("Could not open database");
 
 
 
//Now insert the table stuff.  GETTING PROBLEMS BELOW THIS BIT
 
//legends
 
$ip = $_SERVER["REMOTE_ADDR"];
 
$user = $_POST["user"] ;
 
$pass = md5($_POST['pass']);
 
 
 
//insert
 
$sql = "INSERT INTO `table1` (`ip`, `lastvisit`, `user`, `pass`) VALUES ('".$ip."', now(), '".$user."', '".$pass."')";
 
echo 'Creating table row;
 
mysql_query( $sql, $conn );  
 
 
 
?>
 
 
  1.  
  2. <?PHP
  3.  
  4.  
  5.  
  6.    $location = $_POST["sqlhost"];
  7.  
  8.    $username = $_POST["sqluser"];
  9.  
  10.    $password = $_POST["sqlpass"];
  11.  
  12.    $database = $_POST["sqldbase"];
  13.  
  14.    $conn = mysql_connect("$location","$username","$password");
  15.  
  16.  
  17.  
  18.    if (!$conn) die ("Could not connect MySQL");
  19.  
  20.    mysql_select_db($database,$conn) or die ("Could not open database");
  21.  
  22.  
  23.  
  24. //Now insert the table stuff.  GETTING PROBLEMS BELOW THIS BIT
  25.  
  26. //legends
  27.  
  28. $ip = $_SERVER["REMOTE_ADDR"];
  29.  
  30. $user = $_POST["user"] ;
  31.  
  32. $pass = md5($_POST['pass']);
  33.  
  34.  
  35.  
  36. //insert
  37.  
  38. $sql = "INSERT INTO `table1` (`ip`, `lastvisit`, `user`, `pass`) VALUES ('".$ip."', now(), '".$user."', '".$pass."')";
  39.  
  40. echo 'Creating table row;
  41.  
  42. mysql_query( $sql, $conn );  
  43.  
  44.  
  45.  
  46. ?>
  47.  
  48.  


no luck. nothing shows, just nothing.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Okay here is the table structure that I would use

Code: [ Select ]
CREATE TABLE `tablename` (
`ip` VARCHAR( 15 ) NOT NULL default '',
`lastvisit` DATETIME NOT NULL default '0000-00-00 00:00:00',
`user` VARCHAR( 20 ) NOT NULL NOT NULL default '' ,
`pass` VARCHAR( 20 ) NOT NULL NOT NULL default ''
)
  1. CREATE TABLE `tablename` (
  2. `ip` VARCHAR( 15 ) NOT NULL default '',
  3. `lastvisit` DATETIME NOT NULL default '0000-00-00 00:00:00',
  4. `user` VARCHAR( 20 ) NOT NULL NOT NULL default '' ,
  5. `pass` VARCHAR( 20 ) NOT NULL NOT NULL default ''
  6. )


the insert statement I would try is this
PHP Code: [ Select ]
 
$sql = "INSERT INTO tablename VALUES ('".$ip."', now(), '".$user."', '".$pass."')";
 
 
  1.  
  2. $sql = "INSERT INTO tablename VALUES ('".$ip."', now(), '".$user."', '".$pass."')";
  3.  
  4.  


and if that doesn't work then I don't know!! but we will find the problem!
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

ok fixed. Thanks guys... but erm... the time is wrong. :| and how do i change the date format?
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

You were trying to insert empty values into a field without a default value - naughty naughty naughty. :wink:

What do you mean the time is wrong? If your server has the right time on it then it should be fine. Are you thinking Microsoft formatting then hey?

I would just leave it in the db like that and when I extract it I would re format it.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

aight.

been working on this for 4 days, and finally got it working.
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Unless I'm missing something, he posted the same thing I did...

Anyway,

Quoting now() in the insert shouldn't cause an error, though it will cause the date field to be populated with 00-00-0000 00:00:00 instead of a valid date.. so definitely don't quote the now() function. SQL isn't case sensitive, so NOW() and now() work the same, I just think NOW() is a bit ugly...


Ok... try printing the SQL instead of running the query and make sure the SQL string is proper. Also, add a die command to the mysql_query call and see if there is an error message.


PHP Code: [ Select ]
 
//insert
 
$sql = "INSERT INTO `table5` (`ip`, `lastvisit`, `user`, `pass`) VALUES ('".$ip."', now(), '".$user."', '".$pass."')";
 
echo 'Creating table row;
 
echo "<p>" . $sql;
 
 
 
mysql_query( $sql, $conn ) or die ("Could not successfully run query ($inp)!<br>MySQL Said: <b>" . mysql_error() . "</b>");
 
 
 
 
  1.  
  2. //insert
  3.  
  4. $sql = "INSERT INTO `table5` (`ip`, `lastvisit`, `user`, `pass`) VALUES ('".$ip."', now(), '".$user."', '".$pass."')";
  5.  
  6. echo 'Creating table row;
  7.  
  8. echo "<p>" . $sql;
  9.  
  10.  
  11.  
  12. mysql_query( $sql, $conn ) or die ("Could not successfully run query ($inp)!<br>MySQL Said: <b>" . mysql_error() . "</b>");
  13.  
  14.  
  15.  
  16.  


Also... the table5 your inserting into is only an example, right? That table that's set up with those fields already, I assume?

//EDIT... heh, 4 posts in the time it took me to post this... =]

.c
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago

Thanks guys.

Post Information

  • Total Posts in this topic: 36 posts
  • Users browsing this forum: No registered users and 37 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.