PHP Mysql insert

hargi

hargi

hargi

Associate
Joined
19 Jul 2006
Posts
1,847
Have been struggling all morning to get a form to insert to a database so simplified everything to this
PHP: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
$hostname_conn = "localhost";
$username_conn = "*****";
$password_conn = "********";
$conn = mysql_connect($hostname_conn, $username_conn, $password_conn); 
if (!$conn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Impact",$conn);
mysql_query("INSERT INTO test (1, 2)
VALUES ('Pete', 'Grif')");
mysql_close();
?>
</body>
</html>

now when i run that page i get a blank page displayed but when i check the database nothing has been added.
First is the code correct? as i think the variabals are ok
 
YEY got it to connect now using phpmyadmin i generated this code
PHP: 
$sql = 'INSERT INTO `hargit00_Impact`.`Entries` (`Username`, `School`, `Dateofvisit`, `Tspent`, `Activity`, `Management`, `Coords`, `In_School`, `PCP_BSF`, `Learning_Platform`, `SRF`, `eSafety`, `Projects`, `Reprographics`, `FS`, `Evolve`, `Other`, `Description`, `Impact`, `Action_Plan`, `Success_Criteria`, `Notes`, `Entry`) VALUES (\'username\', \'school\', \'2009-10-28\', \'3\', \'Activity\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'0\', \'Description\', \'Impact\', \'Action_plan\', \'Success\', \'Notes\', NULL);';

this just imputs the values into the database.
How do i change this from text entry into variables so the username to the value of $name

TIA
 
hargi said:
YEY got it to connect now using phpmyadmin i generated this code
PHP: 
$sql = 'INSERT INTO `hargit00_Impact`.`Entries` (`Username`, `School`, `Dateofvisit`, `Tspent`, `Activity`, `Management`, `Coords`, `In_School`, `PCP_BSF`, `Learning_Platform`, `SRF`, `eSafety`, `Projects`, `Reprographics`, `FS`, `Evolve`, `Other`, `Description`, `Impact`, `Action_Plan`, `Success_Criteria`, `Notes`, `Entry`) VALUES (\'username\', \'school\', \'2009-10-28\', \'3\', \'Activity\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'1\', \'0\', \'Description\', \'Impact\', \'Action_plan\', \'Success\', \'Notes\', NULL);';
this just imputs the values into the database.
How do i change this from text entry into variables so the username to the value of $name

TIA
Click to expand...

Set the form action to post:

Code: 
<form action="you-php-page.php" method="post"> ..........
Give your input a name like:

Code: 
<input type="text" name="myName" />
In your php page use

PHP: 
$name = $_POST['myName']
to get the contens of the form. However, you should read up on santising your input and http://php.robm.me.uk/ .

EDIT: you'd need to slightly change your sql query too to something like:

PHP: 
$sql = sprintf("INSERT INTO myTable (username) VALUES ('%s')", 
                            mysql_real_escape_string($name));
 
Last edited:
thanks for the reply but whats the %s in
PHP: 
$sql = sprintf("INSERT INTO myTable (username) VALUES ('%s')", 
                            mysql_real_escape_string($name));
 
PHP: 
sprintf("The %s of %s is %s!", 'colour', 'my apple', 'red' )
= The colour of my apple is red!

PHP: 
sprintf("The %s of %s is %s!", 'sound', 'cows', 'moo' )
= The sound of cows is moo!

Sprintf is just a string replacement function, but it helps to keep your code really neat. You pass as parameters one string for each %s etc you want to replace (you could use variables too).

http://uk2.php.net/sprintf
 
Last edited:
Sorry not getting this at all
PHP: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
$hostname_conn = "localhost";
$username_conn = "****";
$password_conn = "******";
$var1 = "test";
$var2 = "t123";
$conn = mysql_connect($hostname_conn, $username_conn, $password_conn); 
if (!$conn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("hargit00_Impact");


$sql = sprintf("INSERT INTO test (1,2) VALUES ('%s','%s')", 
                            mysql_real_escape_string($var1),
							mysql_real_escape_string($var2));

mysql_query($sql);
mysql_close();
?>
</body>
</html>
Adds nothing to the database at all

but if i change the $sql to
PHP: 
$sql = 'INSERT INTO `hargit00_Impact`.`test` (`1`, `2`) VALUES (\'text\', \'text\');';

it works

its driving me crazy
 
Try..

PHP: 
$sql = sprintf("INSERT INTO `hargit00_Impact`.`test` (`1`, `2`) VALUES ('%s','%s')", 
                            mysql_real_escape_string($var1),
                            mysql_real_escape_string($var2));
 
Pho my friend you are a freeking genius that worked straight away :)

Lets see if i can make it work with the propper form data
 
Thanks for that tip suarve :) helped with some of my other problems
PHP: 
<?php require_once('Connections/conn.php');
$name=$_POST[username];
$school=$_POST[School];
$theDate1 = isset($_REQUEST["date1"]) ? $_REQUEST["date1"] : "";
$theDate2 = isset($_REQUEST["date2"]) ? $_REQUEST["date2"] : "";

mysql_select_db($database_conn, $conn);
$query = sprintf("SELECT * FROM Entries WHERE Username LIKE '%s' AND School LIKE '%s' AND Dateofvisit BETWEEN '%s' AND '%s'",  
mysql_real_escape_string($name),
mysql_real_escape_string($school),
mysql_real_escape_string($theDate1),
mysql_real_escape_string($theDate2));

$result = mysql_query($query);
?>

That query works fine when proper name or school is sent via the form on the page before.
Code: 
<label>Username
      <select name="username" size="1" id="username">
        <option value="John">John</option>
        <option value="Pete">Pete</option>
        <option value="Jane">Jane</option>
        <option value="*" selected="selected">All</option>
      </select>
    </label>

but i want an option to search all which i thought i could do by putting a * there but when i select all in the query it puts the * in '*'
Code: 
SELECT * FROM Entries WHERE Username LIKE '*' AND School LIKE 'Pre-school' AND Dateofvisit BETWEEN '2007-11-01' AND '2011-11-01'

is there a way around this

TIA
 
Is it possible to have 2 queries in one page
the first query seams to work ok but the second doesnt seam to do anything
echo $timequery; returns nothing.

PHP: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>display</title>
<?php require_once('Connections/conn.php');
$arrange=$_POST[arrange];
$name=$_POST[username];
$school=$_POST[School];
$theDate1 = isset($_REQUEST["date1"]) ? $_REQUEST["date1"] : "";
$theDate2 = isset($_REQUEST["date2"]) ? $_REQUEST["date2"] : "";
// set up query for table data
mysql_select_db($database_conn, $conn);
$query = sprintf("SELECT * FROM Entries WHERE Username LIKE '%s' AND School LIKE '%s' AND Dateofvisit BETWEEN '%s' AND '%s' ORDER BY '%s'",  
mysql_real_escape_string($name),
mysql_real_escape_string($school),
mysql_real_escape_string($theDate1),
mysql_real_escape_string($theDate2),
mysql_real_escape_string($arrange));
$result = mysql_query($query);

//set up query to get total time from above search
mysql_select_db($database_conn, $conn);
$timequery = mysql_query(sprintf ("SELECT SUM (Tspent) as fCount FROM Entries WHERE Username LIKE '%s' AND School LIKE '%s' AND Dateofvisit BETWEEN '%s' AND '%s'",  
mysql_real_escape_string($name),
mysql_real_escape_string($school),
mysql_real_escape_string($theDate1),
mysql_real_escape_string($theDate2)));

//$tresult = mysql_fetch_array($timequery);
//$fCount = $tresult['fCount'];
?>
</head>

<body>

<?php echo $timequery; 
//echo $tresult;
// display table data
while ($row = mysql_fetch_array ($result))
									{
$school=($row['School']); 
$date=($row['Dateofvisit']);
	echo "<tr>";
	echo "<td>$school</td>";
	echo "<td>$date</td>";
	echo "</tr>";

									}
//Display total time spent


?>

</body>
</html>
 
After the second query add:

$result2 = mysql_query($query);

Currently you're not actually executing the query. Notice how I've called it $result2 to stop it from overwriting the first $result.

Then add another:
while ($row = mysql_fetch_array ($result2))
}

To your code at the bottom to loop through the data and display what you want.


BTW: you don't have to put each item in sprintf on a new line, you could write it as:
PHP: 
$timequery = mysql_query(sprintf ("SELECT SUM (Tspent) as fCount FROM Entries WHERE Username LIKE '%s' AND School LIKE '%s' AND Dateofvisit BETWEEN '%s' AND '%s'",  mysql_real_escape_string($name), mysql_real_escape_string($school), mysql_real_escape_string($theDate1), mysql_real_escape_string($theDate2)));
 
Pho said:
After the second query add:

$result2 = mysql_query($query);

Currently you're not actually executing the query. Notice how I've called it $result2 to stop it from overwriting the first $result.
Click to expand...

as a rule of thumb I put in
$result2 = mysql_query($query2) or die ("getting query 2 failed! what kinda coder are you!?);
Click to expand...
if you have it in developement stage i.e nobody other than you will see it atm, you can use:
$result2 = mysql_query($query2) or die (mysql_error());
Click to expand...
and it will output a more useful error response. but if you are finished with that query remove the "mysql_error()" bit as it can be used to gain access to your table design

I do the die so if it fails I know where to go. I call it query 2 so I can use it again should I need to. easier than rewriting a whole query again :p

and for your connection data put it in a seperate file and use

include("connection.php");
Click to expand...
so you don't have to keep re-writting it.
 
Last edited:
PHP: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>display</title>
<?php require_once('Connections/conn.php');
$arrange=$_POST[arrange];
$name=$_POST[username];
$school=$_POST[School];
$theDate1 = isset($_REQUEST["date1"]) ? $_REQUEST["date1"] : "";
$theDate2 = isset($_REQUEST["date2"]) ? $_REQUEST["date2"] : "";
// set up query for table data
mysql_select_db($database_conn, $conn);
$query = sprintf("SELECT * FROM Entries WHERE Username LIKE '%s' AND School LIKE '%s' AND Dateofvisit BETWEEN '%s' AND '%s' ORDER BY '%s'",  
mysql_real_escape_string($name),
mysql_real_escape_string($school),
mysql_real_escape_string($theDate1),
mysql_real_escape_string($theDate2),
mysql_real_escape_string($arrange));
$result = mysql_query($query);

//set up query to get total time from above search

$timequery = mysql_query(sprintf ("SELECT SUM (Tspent) as fCount FROM Entries WHERE Username LIKE '%s' AND School LIKE '%s' AND Dateofvisit BETWEEN '%s' AND '%s'",  mysql_real_escape_string($name), mysql_real_escape_string($school), mysql_real_escape_string($theDate1), mysql_real_escape_string($theDate2))); 

$result2 = mysql_query($timequery) or die ("getting query 2 failed! what kinda coder are you!?");

?>
</head>

<body>

<?php 
while ($row = mysql_fetch_array ($result2)) {
	echo $row;
}

// display table data
while ($row = mysql_fetch_array ($result))
									{
$school=($row['School']); 
$date=($row['Dateofvisit']);
	echo "<tr>";
	echo "<td>$school</td>";
	echo "<td>$date</td>";
	echo "</tr>";

									}
//Display total time spent


?>

</body>
</html>

Brings up "getting query 2 failed! what kinda coder are you!?"

if i get rid of the code
PHP: 
$result2 = mysql_query($timequery) or die ("getting query 2 failed! what kinda coder are you!?");
and
PHP: 
while ($row = mysql_fetch_array ($result2)) {
	echo $row;
}

and try echoing the $timequery out which should give me the SQL statment its just blank.
 
To begin with you've put "mysql_query" twice. you only put it in once, you can either put it in the initial "$query" or in the "$result" having it twice has killed it.

But the problem lies deeper in your query as it isn't echoing any results from your query. Can't you format your results *after* you've got them in their variables? you can do this in the while loop.

personally I keep my queries seperate from extra code:

$timequery = mysql_query(sprintf ("SELECT SUM (Tspent) as fCount FROM Entries WHERE Username LIKE '%s' AND School LIKE '%s' AND Dateofvisit BETWEEN '%s' AND '%s'");
Click to expand...

also - what are you using
mysql_real_escape_string($name);
mysql_real_escape_string($school);
mysql_real_escape_string($theDate1);
mysql_real_escape_string($theDate2);
Click to expand...

for? are you trying to format the data before it is set in result? or is it a condition being in the where?

try removing filters to get it working - when debugging you basically need to disect it bit by bit, pulling off the more difficult parts of the query to see where you've failed. I suggest removing the above 4 conditions first, then the between condition (I have a feel this could be your issue)

remember that mysql_query is a php command to interact with mysql, so using mysql_query() anything between those brackets MUST (I think) be mysql compliant. mysql_real_escape_string is a php command I *THINK* so that might also be an issue.

also what kind of error do you get when you change
$result2 = mysql_query($timequery) or die ("getting query 2 failed! what kinda coder are you!?");
Click to expand...

with
$result2 = mysql_query($timequery) or die (mysql_error());
Click to expand...

you definately have a sql error in your code. just which part we need to find out.

you don't need to remove the while statement as php is dead before it gets there so it's not written
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom