Dynamic Query Question

[PCHomepage]

I'm not a newbie but I am quite rusty at this. I have a dynamically-generated UPDATE query from a form submission that is not giving any errors but neither is it updating. I'm sure I've missed something obvious as it doesn't appear that it is actually doing anything with the query. Can someone take a look?

$query = "UPDATE recordings SET Title = 5, Format = 10, CatalogNo = 'DMO-55458', AutoLinked = 1, Description = 'Test Data', KeyWords = 'Test Data', Label = 50, Country = 12, Year = 1962, YearExact = 1, MonoStereo = 2, LinerNotes = 32, TracksAssigned = 11155, SpecialTracks = 'Test Data', Bootleg = 0, GeneralHistory = NULL, TitleHistory = 1, ArgHistory = 16, PressingHistory = 'Test Data', ReviewID = NULL, YearSort = 1943, ArchiveCopy = 1, ItemsID = 1024722449, EntryVerified = 1 WHERE ID = 220472229";

mysql_query($query);

Thanks in advance.

Don

 

[r937]

try your query outside of php and see what you get

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Thanks for the excellent idea! It does indeed give an error on the Description field but it (HeidiSQL) isn't specific about what the error is. The field has had slashes added and has single quotes that use them in the test data but is there something else than needs to be done to a text field other than addslashes()?

Is there some way to return any MySQL error to a variable that can be echoed to the screen? If so, I can replace my current "Record Updated" message with one that actually tells me something useful.

Don

 

[r937]

ah, good old heidi

i thought maybe your description column was called DESC but it isn't

could you run this query in heidi, please --

SHOW CREATE TABLE recordings

also, is that the exact query you ran, with those actual 'Test Data' values, or something else?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

The sample I posted was edited - the actual data contained descriptive text. Not a much, just a sentence or two, but others have a bit of HTML (links mainly but also other formatting). I also tested with an entry that had only a word or two without any formatting whatsoever and it also gave the same error.

Below is the full table description:

CREATE TABLE `recordings` (
  `ID` bigint(30) NOT NULL AUTO_INCREMENT,
  `Format` int(3) DEFAULT NULL,
  `CatalogNo` varchar(50) DEFAULT NULL,
  `Title` int(4) DEFAULT NULL,
  `Description` text,
  `KeyWords` varchar(255) DEFAULT NULL,
  `Label` int(5) DEFAULT NULL,
  `Country` int(3) DEFAULT NULL,
  `Year` int(4) DEFAULT NULL,
  `YearExact` bit(1) NOT NULL,
  `YearSort` smallint(5) unsigned DEFAULT NULL,
  `MonoStereo` tinyint(4) DEFAULT NULL,
  `LinerNotes` int(3) DEFAULT NULL,
  `TracksAssigned` bigint(20) DEFAULT NULL,
  `SpecialTracks` text,
  `GeneralHistory` tinyint(3) DEFAULT NULL,
  `TitleHistory` tinyint(3) unsigned DEFAULT NULL,
  `PressingHistory` text,
  `ArgentinaHistory` tinyint(2) unsigned DEFAULT NULL,
  `ReviewID` tinyint(2) unsigned DEFAULT NULL,
  `ArchiveCopy` bit(1) NOT NULL,
  `Bootleg` bit(1) NOT NULL,
  `ItemsID` int(10) unsigned DEFAULT NULL,
  `EntryVerified` bit(1) NOT NULL,
  `AutoLinked` bit(1) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=220472404 DEFAULT CHARSET=utf8

Thanks,
Don

 

[vacunita]

You can use the mysql_error() function to output any error generated by the query.

mysql_query($query)[red] or die(mysql_error())[/red];

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[PCHomepage]

Thank you, yes, I know how to do that but can I output the error itself to a variable name so that it becomes part of the form's messages something like this

mysql_query($query) or $MessageBox = mysql_error();

or even redirect to a message page?

Don

 

[PCHomepage]

Not expecting it to work, I hadn't tried my error message example but it does indeed work! Now the messages are much cleaner and keep me on the form.

That said, here is the error message with some actual data:

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 'on, Description = 'Part of a series of CD releases from Promo Sound, formerly ' at line 4

Don

 

[vacunita]

You can just call the mysql_error function after the fact if required then.

mysql_query($query); 
$MessageBox=mysql_error();

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[r937]

[red]near 'on, Description = ...[/red]

could you show the entire query please?

the first time you posted, you had '= 1," just in front of Description, but the error message says you have "on," just in front of Description

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

The query I posted was complete from a test submission but the error presents only part of it.

Yes, I noticed the "on" and corrected it earlier (one checkbox had no value set). Also, after back-ticking all the field names, it's now getting the error a little farther along in the query so I've modified several fields from bit to tinyint(1) and set the default to 0 for those. I also removed all the NULL values from these fields and populated them with 0.

There is still an error somewhere but now I have the actual error number which seems to confirm that there is some mismatch between the field types and data being submitted.

Error no: 1064
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 ' `Bootleg` = ', `GeneralHistory` = 7, `TitleHistory` = 0, `ArgHistory`' at line 14

Here is the actual query generating this message:

UPDATE recordings SET `Title` = 28, `Format` = 3, `CatalogNo` = 'Promo', `AutoLinked` = 1, `Description` = 'Promotional sampler compact disc with remix tracks from various other releases.', `KeyWords` = ', `Label` = 28, `Country` = 3, `Year` = 1999, `YearExact` = 1, `MonoStereo` = 1, `LinerNotes` = 51, `TracksAssigned` = 11099, `SpecialTracks` = ', `Bootleg` = 0, `GeneralHistory` = 7, `TitleHistory` = 0, `ArgHistory` = 0, `PressingHistory` = 'Promotional compact disc ', `ReviewID` = 0, `YearSort` = 5000, `ArchiveCopy` = 1, `ItemsID` = 1005699390, `EntryVerified` = 1 WHERE ID = 220472147

and here is the table structure as it now is:

CREATE TABLE `recordings` (
  `ID` bigint(30) NOT NULL AUTO_INCREMENT,
  `Format` int(3),
  `CatalogNo` varchar(50) DEFAULT NULL,
  `Title` int(4),
  `Description` text,
  `KeyWords` varchar(255) DEFAULT NULL,
  `Label` int(5),
  `Country` int(3),
  `Year` int(4),
  `YearExact` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `YearSort` smallint(5) unsigned,
  `MonoStereo` tinyint(4) unsigned DEFAULT '0',
  `LinerNotes` int(3),
  `TracksAssigned` bigint(20),
  `SpecialTracks` text,
  `GeneralHistory` tinyint(3),
  `TitleHistory` tinyint(3) unsigned,
  `PressingHistory` text,
  `ArgHistory` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `ReviewID` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `ArchiveCopy` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Bootleg` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ItemsID` int(10) unsigned NOT NULL DEFAULT '0',
  `EntryVerified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `AutoLinked` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=220472404 DEFAULT CHARSET=utf8

Don

 

[PCHomepage]

I think I've narrowed the remaining problem down to the check boxes. Apparently they need to submit 0 if unchecked and I am not sure how to do that so I may have to change them to radio buttons. I am open to suggestions as to field settings or other options to overcome this issue. Anyway, thanks for all the help.

Don

 

[vacunita]

Check boxes aren't submitted if they aren't checked, so your query may have invalid values (same behavior from radio buttons).

What you could do, is build a list of your potential check boxes, and give them all a 0 value. Then just alter the ones that were submitted with the correct values.

For instance:

$checkbox1=0;
$checkbox2=0;
$checkbox3=0;
$checkbox4=0;
$checkbox5=0;
$checkbox6=0;


if(isset($_POST['checkbox1'])){$checkbox1=$_POST['checkbox1'];}
if(isset($_POST['checkbox2'])){$checkbox2=$_POST['checkbox2'];}
if(isset($_POST['checkbox3'])){$checkbox3=$_POST['checkbox3'];}
...

Then you can use those variables in your query. So all your query values are valid values.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech