Query issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

lubber123
Forum Commoner
Posts: 51
Joined: Mon Sep 15, 2008 12:26 pm
Location: Manassas, VA

Query issue

Post by lubber123 »

Can anyone tell me what is wrong with my query string. It is not getting a result and I have taken too long trying to find the issue. Pulling my hair out really.

Code: Select all

 
 
$query = "UPDATE building_maintenance_tickets SET maintenance_notes = '{$notes}' WHERE id = " . $ticket_id;
            
$result = mysql_query($query, $connection); //Confirm that the query was performed
 
 
User avatar
Peter Anselmo
Forum Commoner
Posts: 58
Joined: Wed Feb 27, 2008 7:22 pm

Re: Query issue

Post by Peter Anselmo »

Have you tried having the script output the constructed query before executing it? That would tell you real quick whether the problem was with the query, or with $notes and $ticket_id variables.

You might try this:

Code: Select all

 
$query = "UPDATE building_maintenance_tickets SET maintenance_notes = '{$notes}' WHERE id = " . $ticket_id;
die( $query );
$result = mysql_query($query, $connection); //Confirm that the query was performed
 
if that works as expected, have php output the mysql error like this:

Code: Select all

$result = mysql_query($query, $connection) or die( mysql_error( $connection) ); //Confirm that the query was performed
lubber123
Forum Commoner
Posts: 51
Joined: Mon Sep 15, 2008 12:26 pm
Location: Manassas, VA

Re: Query issue

Post by lubber123 »

Thanks so much for your reply. I have implemented the code and it seems to be working. But, that is part of the problem. I have created a trouble tixcketing system to be used between us and our tenants. They open a ticket, we update it when we have worked on the issue. they can respons ... this goes on until they close the ticket. However, sometimes in the correspondence - at this place in the code - it gives an error and does not update the DB. I am not sure if it is the query or if perhaps the data it is picking up has something in it that causes it to bug out.

Any suggestions?
User avatar
Peter Anselmo
Forum Commoner
Posts: 58
Joined: Wed Feb 27, 2008 7:22 pm

Re: Query issue

Post by Peter Anselmo »

Often the hardest part of debugging is duplicating the error. Try to find out what specific changes will cause it to consistently fail. A few ideas:

Is there a required field in MySql that is being omitted? MySql will ignore the insert/update if that is the case.
Are single or double quotes in text boxes causing problems? If not escaped, they can change the structure of the query.
Are people putting commas in numeric fields? PHP will truncate at the comma, and mess things up.

If you catch any errors in progress, copy and paste the error code here, and I may be more help.
lubber123
Forum Commoner
Posts: 51
Joined: Mon Sep 15, 2008 12:26 pm
Location: Manassas, VA

Re: Query issue

Post by lubber123 »

Okay - so I found that it is tripping when there is an apostraphe. But it only catches it the second time it is updated to the DB (for some unknown reason). However, the error says:
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 't1 Updated by Living Faith staff 12,02,2008 13:49:33 ca
The word that it was hitting was "don't".

However, I have applied trim(htmlentities())
and
mysql_real_escape_string() to the $notes variable. So I don't know how else to circumvent the issue.
User avatar
Peter Anselmo
Forum Commoner
Posts: 58
Joined: Wed Feb 27, 2008 7:22 pm

Re: Query issue

Post by Peter Anselmo »

Hmmmm. mysql_real_escape_string() should take care of that, however there is a similar function in php, addslashes(). I'd give that one a whirl.

Also, try not using htmlentities(). You may be double-escaping the characters, which would cause unpredictable results. If you're concerned about cross-site scripting (as you always should be), you can escape using htmlentities() going from the database to the page, rather than when inserting into the database.
lubber123
Forum Commoner
Posts: 51
Joined: Mon Sep 15, 2008 12:26 pm
Location: Manassas, VA

Re: Query issue

Post by lubber123 »

Okay - so made changes you suggested - still the prob. Would there be a reason that a textarea would create the issue rather than a text box? i am not sure. If you do have time I have posted the code below. If you are unable to check it out - I appreciate the assistance you have given thus far and will continue to work on it. i am new to this so don;t laugh at my novice code - I am kind of teaching this stuff to myself.


Code: Select all

 
<?php  
            if(isset($_POST['ticket_id'])){
                $ticket_id =  $_POST['ticket_id'];
                mysql_real_escape_string($ticket_id);
            }else{
                echo "No ticket number was provided";
            }
            if(isset($_POST['status'])){
                $tenant_status = $_POST['status'];
                mysql_real_escape_string($tenant_status);
            }else{
                echo "No status was provided";
            }
            if(isset($_POST['description'])){
                $tenant_description = $_POST['description'];
                mysql_real_escape_string($tenant_description);
            }else{
                $tenant_description = "";
            }
            
//IF THE TICKET STATUS US STILL LEFT TO OPEN
 
    $date = date("m,d,Y");
    $time = 10800 + time(); 
    $time = date("H:i:s", $time); 
    
if ($tenant_status  == "open"){ 
            //Getting the database info DATABASE
            $query = "SELECT * FROM building_maintenance_tickets WHERE id = " . $ticket_id;
        
            $result = mysql_query($query, $connection); //Confirm that the query was performed
            if ($result) {
                $row = mysql_fetch_array($result);
                    $notes = $row['maintenance_notes'];
                    $company = $row['company'];
                        $coquery = "SELECT company FROM tenants WHERE id = " . $row['company']; //getting the company info from their ID
                            $result = mysql_query($coquery, $connection);
                                if ($result){
                                    $row = mysql_fetch_array($result);
                                        $companyname = $row['company'];
                                        $notes .= "     " . $tenant_description . "    " ;
                                        $notes .= "Updated by ";
                                        $notes .= $row['company'];
                                        $notes .= " on " . $date . " " . $time . "       \r\n\r\n";
                                }else{
                                    $notes = $tenant_description;
                                }
            }
 
            
            [b]mysql_real_escape_string($notes);
            
            //UPDATE DATABASE<br>
                $query = "UPDATE building_maintenance_tickets SET maintenance_notes = '{$notes}' WHERE id = " . $ticket_id;
                
                $result = mysql_query($query, $connection) or die( mysql_error( $connection) ); //Confirm that the query was performed[/b]
 
                if ($result) {
            //EMAIL LIVING FAITH STAFF THAT THE TICKET WAS NOT CLOSED
                    $select_query = "SELECT email_address from notification_emails WHERE work_duty LIKE '%building%'"; //select LFC building maintenance emails
                    $result = mysql_query($select_query, $connection);
                        if($result){
                            while ($emailrow = mysql_fetch_array($result)){
                                $Name = "Living Faith Church "; //senders name 
                                $mainemail = "administration@lfmi.org"; //senders e-mail adress 
                                $recipient = $emailrow['email_address']; //recipient 
                                        $mail_body = "Closure of Building Trouble Ticket #" . $ticket_id . " was denied.  The following reason was provided: " . $tenant_description . ". \r\n\r\n Please click the link below to review the ticket.\r\n
                                                        http://www.lfmi.org/tenants/view_buildi ... php?ticid=" . $ticket_id;
                                $subject = "Building Maintenance Trouble ticket for " . $companyname;   //subject 
                                $header = "From: ". $Name . " <" . $mainemail . ">\r\n"; //optional headerfields 
                        
                                if (mail($recipient, $subject, $mail_body, $header)){//notify event coordinator that the checklist is coming
 
                                }else{
                                     echo "Emails were not sent to Living faith.  Please contact technical support at 703-331-3820";
                                }
                            }
                
                        }else{
                            echo "The query never worked! Contact Living Faith";
                        }
            }else{
                echo "There was a problem with the system.  Please contact Living Faith IT.";
            }
            echo "<br /><span class = \"text\">Thank you.  Living Faith has been notified that the ticket is still open.</span><br />";
}else{
            //Getting the database info DATABASE
            $query = "SELECT * FROM building_maintenance_tickets WHERE id = " . $ticket_id;
        
            $result = mysql_query($query, $connection); //Confirm that the query was performed
            if ($result) {
                $row = mysql_fetch_array($result);
                    $notes = $row['maintenance_notes'];
                    $company = $row['company'];
                        $coquery = "SELECT company FROM tenants WHERE id = " . $row['company']; //getting the company info from their ID
                            $result = mysql_query($coquery, $connection);
                                if ($result){
                                    $row = mysql_fetch_array($result);
                                        $companyname = $row['company'];
                                        $notes .= "" . $tenant_description;
                                        
                                }else{
                                    $notes = $tenant_description;
                                }
            }
            //UPDATE DATABASE - CLOSE ticket
 
            $notes .= "This ticket was closed by " . $companyname . " on " . $date . " at " . $time . ".";
 
            $query = "UPDATE building_maintenance_tickets SET status = 'closed', maintenance_notes = '$notes' 
                        WHERE id = " . $ticket_id;
            $result = mysql_query($query, $connection); //Confirm that the query was performed
                if ($result) {
                    echo "<br /><br /><span class = \"text\">Thank you.  The ticket has been closed.</span>";
                }else{
                    echo "The ticket could not be closed.  Please contact Living Faith IT.";
                }
}
        
?>
 
User avatar
Peter Anselmo
Forum Commoner
Posts: 58
Joined: Wed Feb 27, 2008 7:22 pm

Re: Query issue

Post by Peter Anselmo »

mysql_real_escape_string() and addslashes() return a string, they don't modify it. In other words, you need to set the string they return equal to something.

Here's what you have:

Code: Select all

mysql_real_escape_string($notes);
Here's what you want:

Code: Select all

$notes = mysql_real_escape_string($notes);
lubber123
Forum Commoner
Posts: 51
Joined: Mon Sep 15, 2008 12:26 pm
Location: Manassas, VA

Re: Query issue

Post by lubber123 »

Ah - thanks - I think this is working. I have tested it several times and I think it is working.

So, the emails that are sent all contain multiple slashes like this:

dated by Living Faith staff 12,04,2008 11:02:48 **\\\\ can\\\'t won\\\'t shan\\\'t\r\n Updated by Living Faith Church on 12,04,2008 11:04:52 \\ can\'t shan\'t Updated by Living Faith staff 12,04,2008 11:05:17 can\\\'\\\'\\\'t Updated by Living Faith Church on 12,04,2008 11:07:18 no I can\'t Updated by Living Faith staff 12,04,2008 11:11:09

Before I send the email I must do the htmlentities??

$notes = htmlentities($notes);

This should stop the slashes?
User avatar
Peter Anselmo
Forum Commoner
Posts: 58
Joined: Wed Feb 27, 2008 7:22 pm

Re: Query issue

Post by Peter Anselmo »

Whoah, Most of your quotes are being double-escaped. It should only have one slash before the the single quotes before going into the database. Also, You don't actually need to escape them before sending them in an email, Email uses a string of characters to distinguish the header from the body, not quotes. So they don't need to be escaped for email.

Also, stripslashes() is the function you want to remove them if you need to .

Here's the link to the part of the php manual you should take a look at. Be sure to read through the related functions as well. http://us.php.net/manual/en/function.ge ... es-gpc.php
lubber123
Forum Commoner
Posts: 51
Joined: Mon Sep 15, 2008 12:26 pm
Location: Manassas, VA

Re: Query issue

Post by lubber123 »

Thanks for all the assistance. Looks like it is working now - except the emails - they still have the slashes in them. Is there a different function to remove them?
User avatar
Peter Anselmo
Forum Commoner
Posts: 58
Joined: Wed Feb 27, 2008 7:22 pm

Re: Query issue

Post by Peter Anselmo »

Glad I could help. The stripslashes() function is the only one I know of to remove slashes. I'm not sure if this is an option, but perhaps send the email first, before escaping all the data and putting it into the database. Then, of course, it wouldn't be an issue.

-Peter
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Re: Query issue

Post by Syntac »

I once saw a piece of code that automatically cleanses $_POST and the rest of the superglobals if magic_quotes_gpc is turned on, but unfortunately I'm too tired to reproduce it. :(
lubber123
Forum Commoner
Posts: 51
Joined: Mon Sep 15, 2008 12:26 pm
Location: Manassas, VA

Re: Query issue

Post by lubber123 »

Peter,

Thanks for all the help. I will try and reorganize the code to send the emails first - see how that does. Is there a PHP book you can recommend to me?
User avatar
Peter Anselmo
Forum Commoner
Posts: 58
Joined: Wed Feb 27, 2008 7:22 pm

Re: Query issue

Post by Peter Anselmo »

Lubber,
As far as PHP books go, It depends what you're looking for.

I've read PHP 5 Unleashed and thought it was really good an comprehensive. It's well written and a good reference. However it doesn't talk about database stuff until halfway through - so I'd probably recommend reading it out of order.

I've read PHP Cookbook. It has some good algorithms for solving common problems but it's kinda dumb how often it calls for using PEAR packages for stuff that PHP already has built in.

I've also read Object Oriented PHP. I enjoyed it, and I've reused several chunks of code from the book - but it's not a first book - it skips over many details which you'll probably need.
Post Reply