Page 1 of 2

Query issue

Posted: Mon Dec 01, 2008 2:41 pm
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
 
 

Re: Query issue

Posted: Mon Dec 01, 2008 3:10 pm
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

Re: Query issue

Posted: Tue Dec 02, 2008 9:56 am
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?

Re: Query issue

Posted: Tue Dec 02, 2008 11:06 am
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.

Re: Query issue

Posted: Tue Dec 02, 2008 12:51 pm
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.

Re: Query issue

Posted: Tue Dec 02, 2008 11:21 pm
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.

Re: Query issue

Posted: Wed Dec 03, 2008 12:20 pm
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.";
                }
}
        
?>
 

Re: Query issue

Posted: Wed Dec 03, 2008 6:14 pm
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);

Re: Query issue

Posted: Thu Dec 04, 2008 10:20 am
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?

Re: Query issue

Posted: Thu Dec 04, 2008 10:40 am
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

Re: Query issue

Posted: Thu Dec 04, 2008 3:05 pm
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?

Re: Query issue

Posted: Thu Dec 04, 2008 8:32 pm
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

Re: Query issue

Posted: Thu Dec 04, 2008 8:46 pm
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. :(

Re: Query issue

Posted: Fri Dec 05, 2008 11:16 am
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?

Re: Query issue

Posted: Fri Dec 05, 2008 12:16 pm
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.