I’m trying to delete rows based on a inner join and for some reason, it is failing. if I echo the $sqlRequest with the variables replaced and paste it in my MySQL, it works. Every other queries I do, except this one.
$sqlRequest = 'DELETE j FROM `Active_PSManager`.`jobs` AS j INNER JOIN `Active_PSManager`.`tasks` AS t ON t.GUID = j.AssignedTask WHERE t.Initiator = "' . $setupguid . '";';My guess, because you are not using prepared statements, and your quotes are wrong, it has a syntax error.
I would have to disagree.
I called my method with postman and get the following :
`DELETE j FROM `Active_PSManager`.`jobs` AS j INNER JOIN `Active_PSManager`.`tasks` AS t ON t.GUID = j.AssignedTask WHERE t.Initiator = "E3B687FC-90C8-4EAC-B533-F9C92FEFB556";`
Which I simply paste in my mysql, and it works.
But the same query sent by my php fails.
Does your php code, that executes the query, have error handling, so that you would know if the query results in an error or just if no row(s) are being affected?
Where is the value in the variable coming from? It likely has some non-printing character as part of it (a new-line for example) that when echoed won’t be in the copy and pasted output.
I would use var_dump() on the variable so that you can see the length of the string in addition to what it contains.
Then I would say it is an issue elsewhere in the code, that you didn’t post.
The help you guys are providing is very useful, and I think I am getting closer to the issue. I hadn’t implement error report in a certain area of my code, so I just did and here is what I got :
string 'DELETE j FROMActive_PSManager.jobsAS j INNER JOINActive_PSManager.tasksAS t ON t.GUID = j.AssignedTask WHERE t.Initiator = "E3B687FC-90C8-4EAC-B533-F9C92FEFB556";' (length=174)
And the error I got is :
Connection failed: No database selected
The Active_PsManager is my database
Sounds like you are using mysql_ functions?
Yes, I’m working with mysql, sorry for not mentioning it before…
Aside from, you really should update the sql code to not use functionality that has been removed. You can add that line in and correct the current issue.
Can you clarify what you mean by that? I’m fairly new to mysql and php, so I learn what I need as I go…
The php mysql_ extension has been removed from the latest php 7+ versions. All code using it needs to be updated so that the code will work when you or your web host updates the php version.
Of the two options, the PDO and mysqli extensions, the PDO extension is the best choice. It is the simplest and most consistent, especially when converting old code, where another php feature, magic_quotes, was removed in php5.4. Magic_quotes proved (some) protection against sql injection in external data that may get put directly into an sql query statement. To add back this protection, the simplest method is to use prepared queries, and the php PDO extension provides the simplest and fastest code when dealing with prepared queries.
I might be misunderstanding what you are saying, but I’m using :
$conn = new mysqli($DBConnectionServerName, $DBConnectionUserName, $DBConnectionPassword);
Then build my query and pass it like :
$result = $conn->multi_query($sqlRequest);
I currently have MySQL 5.7.21 installed.
That’s using the php mysqli extension.
The extension that php uses is just how the php code interfaces with the database server.
The msyql (MySQL) database server is separate from the web server/php.
My most recent reply above was based on your answer to this -
That question is with respect to the php error you got and therefore the php code you are using. Your reply implied you are using the obsolete php mysql_ extension/functions/statements.
Hi,
I just wanted to mentioned I found the solution to my DELETE query. I’m still not sure why the DELETE with my join as handled differently, but the proper format looks like :
`DELETE `Active_PSManager`.`jobs` FROM `Active_PSManager`.`jobs` INNER JOIN `Active_PSManager`.`tasks` ON t.GUID = jobs.AssignedTask WHERE tasks.Initiator = "E3B687FC-90C8-4EAC-B533-F9C92FEFB556";`
Thanks for all your help, and I’ll look into upgrading MySQL and PHP to the proper versions!
Or you could just select the database in your code, like we all thought you would understand was the cause of the problem from reading the error message.
I’m kinda of confused. I know you probably have a good understanding of PHP and MySQL but, from the query I sent, you can clearly see that I explicitly specified my database : Active_PSManager .
I do so because I have multiple databses, and they are dynamically specified in my queries.
So the issue here wasn’t me not specifying my database, it was the DELETE not handling the AS J properly in PHP.
I ran the same command on MySQL with DELETE j FROM … and it worked but failed thru PHP.
Anyways, thanks for the help
Not as far as the driver is concerned. The query is irrelevant.
http://php.net/manual/en/mysqli.select-db.php
Now, use PDO and that isn’t an issue, it will just connect to the DNS you specified.