1

Can this be done in a single query? The table has an auto increment field which I need to know the number to fill url field in the table.

Table 
id(AI) | title | url

What I am expecting is something like INSERT INTO table (title,url) VALUES ('name','CONCATENATION OF title AND ID');

I am currently doing this using 2 queries.

1.Writing the fields except URL. Getting the id using mysqli_insert_id()

2.Updating the above written row.

P.S : The Table has other fields as well so changing the db design isnt really possible in this case.

Kishor
  • 1,513
  • 2
  • 15
  • 25
  • either don't use an auto-increment (you better refactor a bad design than keep struggling with it), and even set url as your primary key, or do this concatenation when fetching it, in your application *model* layer –  Dec 09 '12 at 17:20
  • @cyril - I dont think this is a bad design. I am storing a post and the table has id,title,userid,content,timestamp and url. Do you still think its a bad design? I dont think so. – Kishor Dec 09 '12 at 17:27
  • it's a bad design to try to couple your *url* with the *id* (above all when the id is auto generated) –  Dec 09 '12 at 17:35
  • The idea behind it is,getting unique urls, having 2 row identifiers in the url just in case. Title, and row.(Concatenation will be something like $title."-".$id so I can split them and use both.) If you have an alternate suggestion, I would love to hear – Kishor Dec 09 '12 at 17:40
  • See also my answer to http://stackoverflow.com/questions/1211792/mysql-trigger-to-update-a-field-to-the-value-of-id/1485530#1485530 – Bill Karwin Dec 09 '12 at 18:59

2 Answers2

2

It can't be done atomically. In theory, you could SELECT MAX(id) + 1 FROM yourtable, but please, please don't - although this is not guaranteed to give you the right result and is definitely not a safe approach.

This seems like bad practice, anyway. Why not concatenate the title and ID when you fetch it? Why must it be concatenated on insert?

Colin M
  • 13,010
  • 3
  • 38
  • 58
  • Yeah, I wont use MAX on this under any circumstances. Concatenating title and ID is fine, but for keeping the codes to the minimum,for faster execution, I thought I would go this way, if it is possible. – Kishor Dec 09 '12 at 17:17
  • The concatenation of strings is a considerably smaller performance hit than a second round trip query to your MySQL server - which really is the only way to solve this. Remember, there is such a thing as over-optimization. – Colin M Dec 09 '12 at 17:29
  • You got me wrong. If I need to concatenate at the script, instead of fetching the url field from the db, I need to fetch 2 fields, id and title and then process the array. Its a bit complicated against a single query making the url field. But if thats the only way, I will sure go that way. Cheers! ) – Kishor Dec 09 '12 at 17:32
  • I hear ya. But, getting back a second column from a result set is a lot easier than a whole separate query. In cases like this, most of the delay is in network latency, not query speed. Adding an extra column doesn't really affect network latency. And it's not likely to affect query speed by any measurable about. That's the route I would go. – Colin M Dec 09 '12 at 18:01
  • Okay. Point taken Colin! :) – Kishor Dec 09 '12 at 18:03
0

I will not comment on the design of your database -- you are the judge of that. Just bear in mind that the following command gets the next auto-increment-ID for the specified table, and that this number could change in an instant if another user accesses the table before your code can use it.

I am using this code myself in a project for a similar reason to your own, and it works for me because the table is updated only a few times per day and never by more than one person at a time.

 SELECT Auto_increment FROM information_schema.tables WHERE 
 table_name = '$name_of_your_table';

To be clear, this code gets the auto-increment ID that will be given to the next table entry for the specified table.

cssyphus
  • 37,875
  • 18
  • 96
  • 111
  • 1
    Even in the case where you "*know*" that two people will never write to the same table at the same time, this is still dangerous. IT breaks away from the autonomous nature of a traditional query. – Colin M Dec 09 '12 at 22:12