[SOLVED] Not writing to table?

[dh526]

Hi everyone, just as I thought I was getting the hang of this something has baffled me :S

 

I have an Access database, with several tables in it. Some of these are tblAddress and tblCard.

 

Now, I copied and pasted the code from tblAddress 's php file to tblCard 's... obviously I changed all the names and double checked etc...

 

These are the 2 files for tblCard:

card.php

 

<form action="addcard.php" method="POST">
			<table border = 0>

<tr>
<td>Name on Card :</td> <td> <input type='text' name='name' /></td> 
</tr>
<tr>
<td>Number: </td> <td> <input type='text' name='num' /></td>
</tr>

<tr><td>Type: </td> <td> <input type='text' name='type' /></td> 
</tr>

<tr><td>Expiry Date: </td> <td> <input type='text' name='ex' /></td> <td>Please enter in the form of MM/YY</td></tr>

<tr><td>Security number: </td> <td> <input type='text' name='sec' /></td> <td>This is the 3 digit code on the signature strip</td>
</tr>
<tr><td>
</td></tr>
<tr><td> <input type='submit' value='Add Card'></td> <td>  </td></tr>



</form>
</table>

 

and addcard.php

 

<?php

$name= $_POST['name'];
$num= $_POST['num'];
$type= $_POST['type'];
$ex= $_POST['ex'];
$sec= $_POST['sec'];

$conn=odbc_connect('db09','','');

$sql="INSERT INTO tblCard (username, name, type, number, exp, sec) VALUES ('$username', '$name', '$type', '$num', '$ex', '$sec')";


if (odbc_exec($conn,$sql)) 
{
echo "Thank you for registering a Card!";
}
else 
{
echo "Unfortunately there has been a problem - please <a href='card.php'>Try again</a>";
echo $name;
echo $num;
echo $type;
echo $ex;
echo $sec;
echo $username;
}

odbc_close($conn);

?>

 

I just dont see when I can possibly be going wrong, but it must be something simple :S I have checked and checked and checked to make sure my field names are right both from the form and from the database...

 

Also $username is stored in a cookie and works completely in all of my other pages..

 

Arggghhh, help me !!

 

[dh526]

also :

echo $name;
echo $num;
echo $type;
echo $ex;
echo $sec;
echo $username;

 

writes exactly what I want it too :s

 

 

[MadTechie]

add

         // you must set the connection first
         if (odbc_error())
         {
               echo odbc_errormsg($conn);
         }

and see what the error is

[dh526]

Thank you dude,

 

but I tried that and it didn't output anything.

 

I think I can access the odbc ok, it just won't write to this table for no apparent reason ! ARRGGGGG!

 

 

 

[MadTechie]

try this

 

<?php
$name= $_POST['name'];
$num= $_POST['num'];
$type= $_POST['type'];
$ex= $_POST['ex'];
$sec= $_POST['sec'];

if ( !( $conn = odbc_connect( "db09", "",""))) die( "Could not connect to database" ); 

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, number, exp, sec) VALUES ('$username', '$name', '$type', '$num', '$ex', '$sec')" );

/* check for errors */
if (!odbc_execute($sql))
{
echo "Thank you for registering a Card!";
}else {
// you must set the connection first
if (odbc_error())
{
	echo odbc_errormsg($conn);
}
echo "Unfortunately there has been a problem - please <a href='card.php'>Try again</a>";
echo $name;
echo $num;
echo $type;
echo $ex;
echo $sec;
echo $username;
}

odbc_close($conn);

?>

[dh526]

thanks again dude, I really appreciate your efforts

 

I did what you said, and it was promising as it came up with "thank you for registering a card" ..

 

BUT..

 

it didn't actually write anything into the database :S

 

I'm sooo stuck with this ... as I said before, my way worked in other tables in the same database... I'm confuzzled

 

[MadTechie]

LOL, infact that failed.. (bug in you/my/our code)

change

if (!odbc_execute($sql))

to

if (odbc_execute($sql))

and try again you should get an erro this time

[dh526]

lol.... still says..."thank you for registering a card"

 

:s

[MadTechie]

Erm... (just double checked)

odbc_execute — Execute a prepared statement

Returns TRUE on success or FALSE on failure.

 

Can you double check that..!

 

it can't be true and false!

 

also can you ADD

echo odbc_errormsg($conn);

just above the

odbc_close($conn);

[dh526]

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

 

i get this now

 

 

 

[dh526]

more specifically...

 

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.Unfortunately there has been a problem - please Try againabcdeCharles1[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

 

does this help??

 

 

 

[MadTechie]

okay, thats atleast something

lets try something simpler

change

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, number, exp, sec) VALUES ('$username', '$name', '$type', '$num', '$ex', '$sec')" );

to

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username) VALUES ('$username')" );

[dh526]

That outputs : " Thank you for registering a Card! "

 

and it inserts the username into the table..

 

so no errors... progress it seems...

[MadTechie]

okay now repeat, add another field

ie

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name) VALUES ('$username', '$name')" );

then

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type) VALUES ('$username', '$name', '$type')" );

then

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, number) VALUES ('$username', '$name', '$type', '$num')" );

then

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, number, exp) VALUES ('$username', '$name', '$type', '$num', '$ex')" );

then

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, number, exp, sec) VALUES ('$username', '$name', '$type', '$num', '$ex', '$sec')" );

 

when it fails remove that field and continue onto the next one until all but 1 or 2 fields are in, then we're try to fix those

[dh526]

Ok, been through all the options, turns out its the '$num' field that is breaking everything :s and that field is called "number" in the tblCard table and its just saved as a text field.

 

Also, this is an amazing lesson in bug finding ! You've been amazing ! so thank you

[MadTechie]

okay cool,

well num is probably not a reserved word but in your database it is likely a number, so that doesn't "need" quotes so lets try removing them first

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, number, exp, sec) VALUES ('$username', '$name', '$type', $num, '$ex', '$sec')" );

 

EDIT: bug find is basically checking your input then output, then the middle parts, the output we wanted was either an error or a storage, we could of tried this sooner but i wanted the error incase the error said  something like database locked etc

[dh526]

I tried that, but it didn't work

 

It still does the same thing :S also the "number" field in the table is just a normal text field as if you store it as a number a card starting in 0 would be shortened and the 0 removed...

 

Your help is amazing

 

PS. Sorry about the slow reply, I'm pretty new here and didn't realise it went onto page 2... :S plonker I am

 

[MadTechie]

lol, thats okay,

 

okay lets try (don't hate me) rename the field in the database let call it mynum

and try

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, mynum, exp, sec) VALUES ('$username', '$name', '$type', '$num', '$ex', '$sec')" );

 

if it fails try (note the quote)

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, mynum, exp, sec) VALUES ('$username', '$name', '$type', $num, '$ex', '$sec')" );

 

I know what its like to be stuck, i don't use ODBC much and this helps me resolve problems if i do them at a later stage.

[dh526]

 

Thank you lots

 

This worked :

 

$sql = odbc_prepare($conn, "INSERT INTO tblCard (username, name, type, mynum, exp, sec) VALUES ('$username', '$name', '$type', '$num', '$ex', '$sec')" );

 

Seems like number is a reserved word or something else is playing up ... :S anyway, thank you so so much you've been really patient and spent lots of time helping me

 

It makes learning this all the more easier with people around like you to help

 

Thanks a million

 

[MadTechie]

You very welcome,

I just checked here (done a google for odbc reserved words) and its not listed but i think it probably is reserved.

 

oh well problem solved

[dh526]

Suppose you don't get a PHP Recommended badge for nothing eh

 

Thank you again

 

Also, I had a google too, and found this : http://sqlserver2000.databases.aspfaq.com/what-are-reserved-access-odbc-and-sql-server-keywords.html

 

It suggests that with Access number is reserved and as thats what my database is in (probably should have mentioned that if I didn't...), that would be why...

[MadTechie]

You did,

I have an Access database, with several tables in it.

 

Well we both know now

 

Yeah went from Standard User to GURU to PHP Recommended, i find it helps me to help others,

 

rememeber "smart people learn from their mistakes, really smart people learn from other peoples mistakes"

i choose to help others and i learn from it and its nice to help