Pimp my Code?

[cvarcher]

Hey Everyone,

First post to the forum so go easy on me...

So here's the deal... I am writing the php/mysql for a page that updates my database with the values of a member who's membership details have been edited.

I am having problems getting the values to update into the database. I am not getting any errors so I am kinda stuck at this point....

Let me see if I can give you all the details anyone may need to help on the first try:

Connect to the database (no problems):

//Now we can connect to the Database
$hostname = "localhost"; // Our DB server.
$username = "*******"; // The username for the database.
$password = "*******"; // The password for the username above.
$table = "jos_membership"; // The name of the table we are working with.
$dbname = "cvarcher_members"; // This name of the database the table belongs to.

//MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
//@mysql_select_db( "$dbName") or die( "Unable to select database");
$link = mysql_connect($hostname, $username, $password) or die("Could not connect: " . mysql_error());
	print ("Connected to $hostname successfully.......");

$select = mysql_select_db($dbname) or die("Could not connect to database: " . mysql_error());
	print ("Connected to '$dbname' successfully.......");

Then I define my SQL statement:

//Define the sql udpate query
$update = "UPDATE '".$table."' SET application_date='".$_POST['application_date']."' , applicant_fname='".$_POST['applicant_fname']."' , applicant_lname='".$_POST['applicant_lname']."' , membership_type='".$_POST['membership_type']."' , street1='".$_POST['street1']."' , street2='".$_POST['street2']."' , city='".$_POST['city']."' , state='".$_POST['state']."' , zipcode='".$_POST['zipcode']."' , phone='".$_POST['phone']."' , email='".$_POST['email']."' , econtact_fname='".$_POST['econtact_fname']."' , econtact_lname='".$_POST['econtact_lname']."' , econtact_relationship='".$_POST['econtact_relationship']."' , econtact_phone='".$_POST['econtact_phone']."' , family_fname1='".$_POST['family_fname1']."' , family_lname1='".$_POST['family_lname1']."' , family_age1='".$_POST['family_age1']."' , family_fname2='".$_POST['family_fname2']."' , family_lname2='".$_POST['family_lname2']."' , family_age2='".$_POST['family_age2']."' , family_fname3='".$_POST['family_fname3']."' , family_lname3='".$_POST['family_lname3']."' , family_age3='".$_POST['family_age3']."' , family_fname4='".$_POST['family_fname4']."' , family_lname4='".$_POST['family_lname4']."' , family_age4='".$_POST['family_age4']."' , family_fname5='".$_POST['family_fname5']."' , family_lname5='".$_POST['family_lname5']."' , family_age5='".$_POST['family_age5']."' , family_fname6='".$_POST['family_fname6']."' , family_lname6='".$_POST['family_lname6']."' , family_age6='".$_POST['family_age6']."' , member_bal_prev='".$_POST['member_bal_prev']."' , member_cur_dues='".$_POST['member_cur_dues']."' , member_amt_billed='".$_POST['member_amt_billed']."' , member_amt_paid='".$_POST['member_amt_paid']."' , member_bal_due='".$_POST['member_bal_due']."' , member_status='".$_POST['member_status']."' , member_title='".$_POST['member_title']."' , member_notes='".$_POST['member_notes']."' WHERE ID ='".$_POST['ID']."' LIMIT 1";

You will notice my statement is populated with _POST variables from my form and I know that someone may ask to see what this looks like once it is completed so here is the statement once I have run it through php "echo" with dummy form variables.

$update = "  UPDATE 'jos_membership' SET application_date='2008-03-22' , applicant_fname='XXXX' , applicant_lname='XXXX' , membership_type='Family' , street1='2839 Anystreet St.' , street2='' , city='Any City' , state='CA' , zipcode='90210' , phone='80555551212' , email='[e-mail]' , econtact_fname='Mary' , econtact_lname='Jane' , econtact_relationship='Wife' , econtact_phone='8055551313' , family_fname1='Alyssa' , family_lname1='Milano' , family_age1='37' , family_fname2='Adam' , family_lname2='Munster' , family_age2='15' , family_fname3='Andrew' , family_lname3='Munster' , family_age3='13' , family_fname4='Test' , family_lname4='Test' , family_age4='12' , family_fname5='Test' , family_lname5='Test' , family_age5='18' , family_fname6='Test' , family_lname6='Test' , family_age6='15' , member_bal_prev='0' , member_cur_dues='160' , member_amt_billed='160' , member_amt_paid='160' , member_bal_due='0' , member_status='Active' , member_title='President' , member_notes='This is a test to see if the update to the database worked and changed the original text that was stored in the database.' WHERE ID ='148' LIMIT 1  ");"

Then lastly, I execute the query

//Execute the query with the udpate
$result = mysql_query($update);

So.... I don't have any errors to show you or tell you about. I think I have my syntax correct, but I am not sure. The only reason I know that I have a problem is that the data isn't updating in the DB when I go and browse the table with phpMyAdmin

Any help anyone could provide would be greatly appreciated.

Thanks,
Rick

[darkagn]

Hi cvarcher / Rick and welcome to DaniWeb :)

All of your code looks ok to me, so I am going to ask a few obvious questions:

Firstly, is there a record in the jos_membership table with the supplied ID? Is ID the entire Primary Key of the table? - if so, you don't need LIMIT 1 at the end of your table. Are you refreshing the table data in phpMyAdmin? Is $result true?

[cvarcher]

Hey Darkagn,

Thanks for the quick reply.... to answer your questions...

1. Yes there is a record in the DB with ID=148. This query above was to update that existing record.

2. ID is set as the primary (and only key) on the table.

3. I did refresh the page (in phpMyadmin) and didn't see any change. I also did a query to validate that the data had not updated.

4. I did not test "$result=true". Should I do this? I was under the impression that if "$result was not = true I would have thrown a php or sql error.

Let me know and thanks again for your help
- Rick

[urtrivedi]

$dbname = "cvarcher_members";

1) Are you sure about spelling of "varchar"?
2) Have echoed query that is $update variable before executing then exectuted in phpmyadmin sql section?

Do not use dummy resolve actual query by echoing $update before exeuting in php.

[darkagn]

4. I did not test "$result=true". Should I do this? I was under the impression that if "$result was not = true I would have thrown a php or sql error.

Yes, the documentation does seem to imply that. Maybe just try it to make sure?

This is certainly very strange. Maybe try explicitly stating the connection when you call mysql_query, like so:

$result = mysql_query( $update, $link );

Technically, $link isn't required usually, but maybe something weird is happening between the time you connect and the time you run your query. Sorry I can't be more help but as I say your code looks fine.

[darkagn]

1) Are you sure about spelling of "varchar"?
2) Have echoed query that is $update variable before executing then exectuted in phpmyadmin sql section?

Do not use dummy resolve actual query by echoing $update before exeuting in php.

1. I believe that is the name of his database, not a type of field.
2. I believe in his first post he has listed the actual query.

[cvarcher]

Hey urtrivedi,

Yes I am sure about the spelling. I already use this login and connection method with some other queries on my site without issue.

Yes I have echoed the query before executing. You can see it in my original post above.

I have not executed it in phpMyAdmin. That is a good idea. I will give that a try in the morning.

Thanks,
Rick

[darkagn]

Also, if you are receiving an SQL error, it may not necessarily be output to screen. Try this after you run your query:

if( $result == false )
{
  echo mysql_error( $link );
}

Again, $link isn't completely necessary, but just in case...

[cvarcher]

Hey Darkagn,

Ok... I finally caught and error after making the changes you suggested above...

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 ''jos_membership' SET application_date='2008-03-22' , applicant_fname='Rick' , ap' at line 1

To prevent any unnecessary scrolling here is the sql statement as written:

//Define the sql udpate query
$update = "UPDATE '".$table."' SET application_date='".$_POST['application_date']."' , applicant_fname='".$_POST['applicant_fname']."' , applicant_lname='".$_POST['applicant_lname']."' , membership_type='".$_POST['membership_type']."' , street1='".$_POST['street1']."' , street2='".$_POST['street2']."' , city='".$_POST['city']."' , state='".$_POST['state']."' , zipcode='".$_POST['zipcode']."' , phone='".$_POST['phone']."' , email='".$_POST['email']."' , econtact_fname='".$_POST['econtact_fname']."' , econtact_lname='".$_POST['econtact_lname']."' , econtact_relationship='".$_POST['econtact_relationship']."' , econtact_phone='".$_POST['econtact_phone']."' , family_fname1='".$_POST['family_fname1']."' , family_lname1='".$_POST['family_lname1']."' , family_age1='".$_POST['family_age1']."' , family_fname2='".$_POST['family_fname2']."' , family_lname2='".$_POST['family_lname2']."' , family_age2='".$_POST['family_age2']."' , family_fname3='".$_POST['family_fname3']."' , family_lname3='".$_POST['family_lname3']."' , family_age3='".$_POST['family_age3']."' , family_fname4='".$_POST['family_fname4']."' , family_lname4='".$_POST['family_lname4']."' , family_age4='".$_POST['family_age4']."' , family_fname5='".$_POST['family_fname5']."' , family_lname5='".$_POST['family_lname5']."' , family_age5='".$_POST['family_age5']."' , family_fname6='".$_POST['family_fname6']."' , family_lname6='".$_POST['family_lname6']."' , family_age6='".$_POST['family_age6']."' , member_bal_prev='".$_POST['member_bal_prev']."' , member_cur_dues='".$_POST['member_cur_dues']."' , member_amt_billed='".$_POST['member_amt_billed']."' , member_amt_paid='".$_POST['member_amt_paid']."' , member_bal_due='".$_POST['member_bal_due']."' , member_status='".$_POST['member_status']."' , member_title='".$_POST['member_title']."' , member_notes='".$_POST['member_notes']."' WHERE ID ='".$_POST['ID']."' LIMIT 1";

And here is the same statement using php echo with sample form data values:

$update = "  UPDATE 'jos_membership' SET application_date='2008-03-22' , applicant_fname='XXXX' , applicant_lname='XXXX' , membership_type='Family' , street1='2839 Anystreet St.' , street2='' , city='Any City' , state='CA' , zipcode='90210' , phone='80555551212' , email='[e-mail]' , econtact_fname='Mary' , econtact_lname='Jane' , econtact_relationship='Wife' , econtact_phone='8055551313' , family_fname1='Alyssa' , family_lname1='Milano' , family_age1='37' , family_fname2='Adam' , family_lname2='Munster' , family_age2='15' , family_fname3='Andrew' , family_lname3='Munster' , family_age3='13' , family_fname4='Test' , family_lname4='Test' , family_age4='12' , family_fname5='Test' , family_lname5='Test' , family_age5='18' , family_fname6='Test' , family_lname6='Test' , family_age6='15' , member_bal_prev='0' , member_cur_dues='160' , member_amt_billed='160' , member_amt_paid='160' , member_bal_due='0' , member_status='Active' , member_title='President' , member_notes='This is a test to see if the update to the database worked and changed the original text that was stored in the database.' WHERE ID ='148' LIMIT 1  ";

We must be missing something simple because I don't see any error with my syntax....

[cvarcher]

Ok... got it... for some reason, Table Name can not be in single quotes. I removed quotes from around the table name and the script updates beautifully.

Many thanks to my new friend, Darkagn, down under for pointing me in the right direction.