elentz Posted November 28, 2016 Share Posted November 28, 2016 I am getting the following error when running an update query: 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 '.1.address='10.1.10',account.1.sip_server.2.address='dfgga' WHERE id=1' at line 1 This is the Query: mysql_query("UPDATE global_settings SET account.1.sip_server.1.address='$sipserver1',account.1.sip_server.2.address='$sipserver2' WHERE id=$id") or die(mysql_error()); MySql is version 5.1.73 The variables are being passed from the PHP code and the id is correct. I am at a loss as to what is going on. Thanks for any help Quote Link to comment Share on other sites More sharing options...
benanamen Posted November 28, 2016 Share Posted November 28, 2016 You are using obsolete insecure Mysql code that has been completely removed from Php. Your code is also vulnerable to an SQL Injection Attack. There is no fixing your code. You need to use PDO with prepared statements. https://phpdelusions.net/pdo Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted November 28, 2016 Share Posted November 28, 2016 Your entire table layout is obviously bogus. 400 columns are already a warning sign, but when the names contain indexes and aren't even syntactically valid, there's a major design problem. If you want to use SQL, you must understand the relational model and structure your data accordingly. This means you have to analyze the data, identify entities and the relationships between them, define tables and finally store your data in those tables. You can't just dump all your configuration entries into one giant row (or whatever it is you're trying to do). If you don't want to do this work, or if the relational model simply isn't suited for you case, then don't use SQL. Maybe you just need a static XML/JSON/... file. Maybe a document store like MongoDB or CouchDB fits your needs. If you describe specifically what your data looks like and how you're going to use it, I'm sure we can help you with that decision. Quote Link to comment Share on other sites More sharing options...
elentz Posted November 28, 2016 Author Share Posted November 28, 2016 Benanamen, I was unaware that the method I was using was obsolete. I will look into PDO Jacques1, For this topic the table had only two fields. The table that I was going to work with in this instance was only going to have 16 fields at most. You must be referring to a previous post of mine. For the record I broke up those 400 fields I needed into different tables. I am looking into just writing a file at this point. Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted November 28, 2016 Share Posted November 28, 2016 It doesn't matter if you have only 16 fields. When your columns are named "account.1.sip_server.1.address", you clearly don't understand the relational model. But of course you're free to keep trying. Quote Link to comment Share on other sites More sharing options...
elentz Posted November 28, 2016 Author Share Posted November 28, 2016 Ok Jacques1 I get it, you are right. I think I will just use a txt file and write to that instead. I have done some testing this morning and it looks like I can create the file in the format I want. My concern now is being able to take that file and read it back to the html page that wrote it. The users might want to make changes, without starting over. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted November 28, 2016 Share Posted November 28, 2016 Just to clarify, "account.1.sip_server.1.address" is a column name? If so, have you tried using backticks (`)? UPDATE global_settings SET `account.1.sip_server.1.address` = '$sipserver1'... Without backticks, SQL thinks you are trying to use a table alias. Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted November 28, 2016 Share Posted November 28, 2016 I hope that was a joke. Otherwise I suggest the OP teaches you some SQL basics, because unlike you he at least understands the problem. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted November 29, 2016 Share Posted November 29, 2016 (edited) I hope that was a joke. Otherwise I suggest the OP teaches you some SQL basics, because unlike you he at least understands the problem. I don't claim I know everything about SQL, PHP, etc. I have never seen a column name like "account.1.sip_server.1.address". So I apologize for assuming that maybe there was some "magic" going on somewhere. For what it's worth, I just tested a query like what the OP posted. And the query worked once I added the backticks. Note: I'm not saying that "account.1.sip_server.1.address" is a great column name. I'm also not saying that the OPs database table is perfect. I'm just offering a reason why the query failed. That way the OP can be prepared for the next query if he/she chooses to name the columns this way. Edited November 29, 2016 by cyberRobot Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted November 29, 2016 Share Posted November 29, 2016 My point is that the weird column name is the symptom of a deeper problem (a data structure which doesn't fit into the relational model). Yes, you can suppress the symptom in all sorts of ways. You could replace each dot with a “Z”, for example; this technically “works”. But then you still haven't done anything about the actual problem. In fact, it will only get worse as the application becomes more complex. So my suggestion was to either make the data structure compatible with SQL or not use SQL at all. Both can be valid choices depending on the data (which the OP hasn't posted yet). Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted November 29, 2016 Share Posted November 29, 2016 My point is that the weird column name is the symptom of a deeper problem... Sure, I have no argument there. And I am glad you are pointing these types of issues out. I'm learning a lot from your posts. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.