Need a second look

[elentz]

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

[benanamen]

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

[Jacques1]

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.

[elentz]

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.

[Jacques1]

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.

[elentz]

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.

[cyberRobot]

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.

[Jacques1]

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.

[cyberRobot]

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

[Jacques1]

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).

[cyberRobot]

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.