PHP/MYSQL

[Phobia1]

Hi Guys
what once worked well now does not. Here's the simple code that is supposed to add new data to a database.
What has gone wrong. We recently changed to a new host and...

Thanks for help in advice/
regards
Fred

//Newdata.php
<form method=post action="submittedinfo.php">
<table>

<tr><td colspan="2"><font SIZE="+0" face="verdana"></td></tr>
<tr><td height="40"></tr>
<tr><td height="40"><h1>New Data Input </h1></td></tr>

<!-- creating the name/id that will be associated with $ID etc-->
<tr><td align="right"><font SIZE="+0" face="verdana">Property ID <input type="text" name="Ref" id="Ref"></td></tr>
<tr><td align="right"><font SIZE="+0" face="verdana">Our Broker <SELECT NAME="Broker">
<OPTION VALUE=existing SELECTED>Dilyana
<OPTION VALUE=Yourdan>Yourdan
<OPTION VALUE=Kamen>Kamen
<OPTION VALUE=Rayna>Rayna
</SELECT>
</td></tr>
<tr><td align="right"><font SIZE="+0" face="verdana">Contact phone number <input type="text" name="Brokerphone" id="Brokerphone"></td></tr>
<tr><td align="right"><font SIZE="+0" face="verdana">Contact email address <input type="text" name="Brokeremail" id="Brokeremail"></td></tr>
<tr><td align="right"><font SIZE="+0" face="verdana">Comments <input type="text" name="Comments" id="Comments"></td></tr>

<tr><td align="right"><font SIZE="+0" face="verdana"><input type="submit" value="ADD New Data"></td></tr>

</table>
</form>

//submittedinfo.php
// Connect to the Database
if (!($link=mysql_connect($localhost, $username, $password))) {
DisplayErrMsg(sprintf("error connecting to host %s, by user %s",
$localhost,$username;
exit() ;
}

// Select the Database
if (!mysql_select_db($databaseName, $link)) {
DisplayErrMsg(sprintf("Error in selecting %s database", $databaseName)) ;
DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;
exit() ;
}

$sqlquery = "INSERT INTO Database(Ref, Broker,Brokerphone,Brokeremail,Comments) VALUES('{$_POST['Ref']}','{$_POST['Broker']}','{$_POST['Brokerphone']}','{$_POST['Brokeremail']}')";

$results = mysql_query($sqlquery); //Enter the new data

mysql_close();

[Demaestro]

What happens when you run it?

Does it error? If so what is the error message?

You changed hosts but did you migrate your database over? Is it the same schema and data as before?

[Phobia1]

yes its the same, the database I can view, modify and upload files with my origin files but can't add new data, i just get a blank screen

[Demaestro]

I don't see anything wrong with it. Do you know if you versions changed?

Can you can execute insert queries through some admin tool or access the DB directly you can try issuing an insert statement by hand and see if it is entered. Are their permissions set on the DB regarding issuing insert and update statements?

Another thing I would ask is if you have a copy of the database some where and you are mistakenly writing to one but reading from another? I know I have done that and it seems like nothing is being written when it is just being written to the wrong one.

Also add a line to print your sql string to the screen before it executes so you can see if it is properly formed.

I think you use echo... I am not a PHP guy. But something like this should work..

$sqlquery = "INSERT INTO Database(Ref, Broker,Brokerphone,Brokeremail,Comments) VALUES('{$_POST['Ref']}','{$_POST['Broker']}','{$_POST['Brokerphone']}','{$_POST['Brokeremail']}')";

echo $sqlquery;

$results = mysql_query($sqlquery);

[edited by: Demaestro at 5:47 pm (utc) on Dec. 15, 2008]

[Phobia1]

Nor can I. I can read the data as I check with
// Connects to your Database
mysql_connect($localhost, $userName, $password) or die(mysql_error());
mysql_select_db($userName) or die(mysql_error());
$data = mysql_query("SELECT * FROM Garant") or die(mysql_error());
Print "<table border cellpadding=3>";
while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<th>Ref:</th> <td>".$info['Ref'] . "</td> ";
Print "<th>Broker:</th> <td>".$info['Broker'] . " </td></tr>";
}
Print "</table>";

NO PROBLEMS just can't add a new record.

[Phobia1]

I found the problem ! Have to input a key record.
Now I need to know the highest key number (of the last record. This is my kind of attempt. Is it possible to pass the result plus 1 into a hidden form field so that it gets posted with the other records.
Regards
Fred
/ get the last key from the database
mysql_connect($localhost, $userName, $password) or die(mysql_error());
mysql_select_db($userName) or die(mysql_error());
//$results = mysql_query($sqlquery); //Enter the new data
//echo $results;
$data = mysql_query("SELECT akey FROM Garant ORDER BY akey DESC LIMIT 1 ") or die(mysql_error());

//Print "<table border cellpadding=3>";
while($info = mysql_fetch_array( $data ))
{
Print "<th>akey:</th> <td>".$info['akey'] . "</td> ";

}
Print "</table>";
mysql_close();
?>

[Demaestro]

I wouldn't do it by storing the value as a hidden field because you could get more than one person with that value.

Lets say the highest id is 25...

Imagine that a person loads the form so you check for the highest id (25), add 1 (26) and put it into a hidden field. Then that person starts filling out the form... a moment later a second person comes along and loads that form, but the first person hasn't submitted it yet so the highest id is still 25, then you add 1, and place 26 in his form... now you have 2 people filling out a form that has the "nextID" value as 26... It will work for the first person to submit the form, but the second person to submit it will get a duplicate key error.

The best way would be to grab the next_id right before you do the update...

something like this....

$newKey = "select max(key) from table_name"

$newKey += 1

$sqlquery = "INSERT INTO Database(Key, Ref, Broker,Brokerphone,Brokeremail,Comments) VALUES($newKey'{$_POST['Ref']}','{$_POST['Broker']}','{$_POST['Brokerphone']}','{$_POST['Brokeremail']}')";

I hope you understand what I mean. If you have more questions just ask.

[milocold]

Why not convert your key column into an identity field?

- M. Cold

[Phobia1]

Hi
Well I tried it but can't get it to work.
I have this now before my INSERT INTO statement/

$newKey = "select MAX(akey) from table_name";
$result = mysql_query($newKey) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$newKey= .$row['MAX(akey)'].;
$newKey +=1;
echo "<br />";
}
echo $newKey;

[Mahabub]

Dear Phobia1,

This line has error
$newKey= .$row['MAX(akey)'].; :-?

Try the below one..........

$newKey = "select MAX(akey) As NKey from table_name";
$result = mysql_query($newKey) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$newKey= $row['NKey'];
$newKey +=1;
echo "<br />";
}
echo $newKey;

Thanks
Mahabub

[Phobia1]

oops
Thanks