Jump to content

Need a second look


elentz

Recommended Posts

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by cyberRobot
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.