[SOLVED] Create MYSQL Table

tarleton · August 1, 2009

Hi guys got this script I'm working on. At the moment it creates the database fine however it doesn't create the table. Any ideas?

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE module1",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("module1", $con);
$sql = "CREATE TABLE form_data
(
your_team varchar(30),
opp_team varchar(30),
time1 varchar(15),
date1 varchar(15),
g_name varchar(30),
)";

$sql1="INSERT INTO Persons (your_team, opp_team, time1, date1, g_name) VALUES
('$_POST[your_team]','$_POST[opp_team]','$_POST[time1]','$_POST[date1]', '$_POST[g_name]')
";
// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>

phpknight · August 1, 2009

If I were you, I would just create the database and tables in phpMyAdmin as long as you don't have to keep create more of them as people use your app.

tarleton · August 1, 2009

Hi PHPKNIGHT I considered that however I want this to do it automatically so every time someone uses the form they don't have to do it them selves.

phpknight · August 1, 2009

Okay, well create the first one in phpadmin, and then in the box, you'll see exactly how it did it. Then, you can use it for your code to avoid the error.

tarleton · August 1, 2009

Wow I never thought about that thanks mate great help but of course I get fronted by another problem, it now creates the database and table fine but fails in inporting data error is:

Warning: Wrong parameter count for mysql_query() in C:\xampp\htdocs\MODULE\formindb.php on line 35

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE module1",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("module1", $con);
$sql = "CREATE TABLE `module1`.`form_data` (
`your_team` VARCHAR( 30 ) NOT NULL ,
`opp_team` VARCHAR( 30 ) NOT NULL ,
`date1` DATE NOT NULL ,
`time1` TIME NOT NULL ,
`g_name` VARCHAR( 30 ) NOT NULL ,
`result` VARCHAR( 15 ) NOT NULL ,
`writeup` TEXT NOT NULL
) ENGINE = MYISAM ;
";

$sql1="INSERT INTO form_data (your_team, opp_team, time1, date1, g_name, result, writeup) VALUES
('$_POST[your_team]','$_POST[opp_team]','$_POST[time1]','$_POST[date1]', '$_POST[g_name]'. $_POST[result], $_POST[writeup])
";
// Execute query
mysql_query($sql,$sql1,$con);

mysql_close($con);
?>

phpknight · August 1, 2009

Well, make sure the number of fields matches the variables first. But if you want to import the data instead of inserting it, just do this:

INSERT INTO table2 (SELECT * FROM table1)

That will copy all data from the old table into the new one as long as all the fields match.

I guess I am not seeing why you need more than one database. Please explain. Couldn't you just keep track of each user's data in a field like user_ID?

tarleton · August 1, 2009

Perhahaps i should try and explain purpose of the script . Okay so what happens is a have a form where users enter a details from a Sport match. That form then sends data to this script which at creates a database and table then inserts form details. Eventually I hope to make it detect whethere a database already exists and if a table already exists and if it does to just insert the data from the form but haven't got their yet. So my problem atm is that it is not isnerting the data from the form. any ideas?

phpknight · August 1, 2009

Look at the docs for this function:

mysql_query($sql,$sql1,$con);

I usually only send one variable here.

wildteen88 · August 1, 2009

Look at the docs for this function:

mysql_query($sql,$sql1,$con);

I usually only send one variable here.

mysql_query only expects two parameters. The first will be the query. The secound parameter which is optional is the mysql link resource, which in your case is $con. You cannot pass two queries at the same time to mysql_query, only one at a time.

This

mysql_query($sql,$sql1,$con);

should be written as

mysql_query($sql,$con);
mysql_query($sql1,$con);

tarleton · August 1, 2009

This

mysql_query($sql,$sql1,$con);

should be written as

mysql_query($sql,$con);
mysql_query($sql1,$con);

Thanks for that mate however no cigar, still not inporting into database here is my code again.

FORM CODE

<form id="match_report" name="Match Report" method="post" FORM ACTION="formindb.php">
  <table width="480" border="0">
     <tr>
       <td colspan="2"><center><strong>Match Report</strong></center></td>
     </tr>
     <tr>
       <td width="176">Your Clan Name:</td>
       <td width="288"><input type="text" name="your_team" id="Team1" value="Your Clan Name" /></td>
     </tr>
     <tr>
       <td>Opposing Team:</td>
       <td><input type="text" name="opp_team" id="opp_team" value="Opposing Team Name"/></td>
     </tr>
     <tr>
       <td>Date of Match:</td>
       <td><input name="date" type="text" id="date1" value="01/01/2009" size="13"/></td>
     </tr>
     <tr>
       <td>Time of Match:</td>
       <td><input name="time" type="text" id="time1" value="4:15 PM" size="13"/></td>
     </tr>
     <tr>
       <td>Game Name:</td>
       <td><input type="text" name="g_name" id="g_name" value="Game Played"/></td>
     </tr>
     <tr>
       <td><label>
      
       </label>
     
Result:</td>
       <td><input name="result" type="text" id="result" value="11-3" size="10"/></td>
     </tr>
     <tr>
       <td>Write Up:</td>
       <td><textarea name="writeup" id="writeup" cols="45" rows="5"></textarea></td>
     </tr>
     <tr>
       <td colspan="2"><label>
         <input type="submit" name="submit" id="submit" value="Submit" />
       </label></td>
     </tr>
  </table>

formindb.php

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE module1",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("module1", $con);
$sql = "CREATE TABLE `module1`.`form_data` (`your_team` VARCHAR(30) NOT NULL, `opp_team` VARCHAR(30) NOT NULL, `date1` VARCHAR(15) NOT NULL, `time1` VARCHAR(15) NOT NULL, `g_name` VARCHAR(30) NOT NULL, `result` VARCHAR(15) NOT NULL, `writeup` TEXT NOT NULL, PRIMARY KEY (`your_team`)) ENGINE = MyISAM;";

$sql1="INSERT INTO form_data (your_team, opp_team, date1, time1, g_name, result, writeup) VALUES
('$_POST[your_team]','$_POST[opp_team]','$_POST[time1]','$_POST[date1]', '$_POST[g_name]'. $_POST[result], $_POST[writeup])
";
// Execute query
mysql_query($sql,$con);
mysql_query($sql1,$con);
mysql_close($con);
?>

GingerRobot · August 1, 2009

I'd start by adding some error checking:

mysql_query($sql,$con) or trigger_error("Error in query: $sql <br>Error was:" . mysql_error(),E_USER_ERROR);
mysql_query($sql1,$con) or trigger_error("Error in query: $sql1 <br>Error was:" . mysql_error(),E_USER_ERROR);

tarleton · August 1, 2009

tal error: Error in query: INSERT INTO form_data (your_team, opp_team, date1, time1, g_name, result, writeup) VALUES ('','','','', ''. , )

Error was:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. , )' at line 2 in C:\xampp\htdocs\MODULE\formindb.php on line 27 No idea what that even means. So new to php any idea?

wildteen88 · August 1, 2009

Values needs to wrapped in quotes in your queries.

$sql1="INSERT INTO form_data (your_team, opp_team, time1, date1, g_name, result, writeup) VALUES
('$_POST[your_team]','$_POST[opp_team]','$_POST[time1]','$_POST[date1]', '$_POST[g_name]', '$_POST[result]', '$_POST[writeup]')

";

You should sanitise user input before placing it directly into to your queries. It is not recommend to place raw $_POST, $_GET data etc into queries.

tarleton · August 1, 2009

by sanitize do u mean adding:

$your_team=$_POST[result]

at the begining of the page

wildteen88 · August 1, 2009

By sanitize I meant pass your variables to mysql_real_escape_string before placing them in your queries.

tarleton · August 1, 2009

Thanks mate, still giving error:

Fatal error: Error in query: INSERT INTO form_data (your_team, opp_team, date1, time1, g_name, result, writeup) VALUES ('','','','', ''. '', '')
Error was:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. '', '')' at line 2 in C:\xampp\htdocs\MODULE\formindb.php on line 35

<?php
$your_team = mysql_real_escape_string($_POST['your_team']);
$opp_team = mysql_real_escape_string($_POST['opp_team']);
$time1 = mysql_real_escape_string($_POST['time1']);
$date1 = mysql_real_escape_string($_POST['date1']);
$g_name = mysql_real_escape_string($_POST['date1']);
$result = mysql_real_escape_string($_POST['result']);
$writeup = mysql_real_escape_string($_POST['writeup']);
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE module1",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("module1", $con);
$sql = "CREATE TABLE `module1`.`form_data` (`your_team` VARCHAR(30) NOT NULL, `opp_team` VARCHAR(30) NOT NULL, `date1` VARCHAR(15) NOT NULL, `time1` VARCHAR(15) NOT NULL, `g_name` VARCHAR(30) NOT NULL, `result` VARCHAR(15) NOT NULL, `writeup` TEXT NOT NULL, PRIMARY KEY (`your_team`)) ENGINE = MyISAM;";

$sql1="INSERT INTO form_data (your_team, opp_team, date1, time1, g_name, result, writeup) VALUES
('$your_team','$opp_team','$date1','$time1', '$g_name'. '$result', '$writeup')
";
// Execute query
mysql_query($sql,$con) or trigger_error("Error in query: $sql <br>Error was:" . mysql_error(),E_USER_ERROR);
mysql_query($sql1,$con) or trigger_error("Error in query: $sql1 <br>Error was:" . mysql_error(),E_USER_ERROR);
mysql_close($con);
?>

wildteen88 · August 1, 2009

Remove the . after '$g_name'.

Also these lines

$your_team = mysql_real_escape_string($_POST['your_team']);
$opp_team = mysql_real_escape_string($_POST['opp_team']);
$time1 = mysql_real_escape_string($_POST['time1']);
$date1 = mysql_real_escape_string($_POST['date1']);
$g_name = mysql_real_escape_string($_POST['date1']);
$result = mysql_real_escape_string($_POST['result']);
$writeup = mysql_real_escape_string($_POST['writeup']);

Should be after you connect to mysql. mysql_real_escape_string will only work if you're connected to mysql first.

tarleton · August 1, 2009

Hi mate. Yea I read that after I posted, moved it after connection string however still no luck

GingerRobot · August 1, 2009

Hi mate. Yea I read that after I posted, moved it after connection string however still no luck

Did you also do this?:

Remove the . after '$g_name'.

You should have:

$sql1="INSERT INTO form_data (your_team, opp_team, date1, time1, g_name, result, writeup) VALUES
('$your_team','$opp_team','$date1','$time1', '$g_name', '$result', '$writeup')
";

tarleton · August 1, 2009

Ah thanks mate your a genuis SOLVED.

GingerRobot · August 1, 2009

Ah thanks mate your a genuis SOLVED.

Superb. I get the credit and wildteen did all the work

Don't forget to mark your topic as solved (You'd already done it).

Sign In

[SOLVED] Create MYSQL Table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information