SQL Insert value from array

Question

So I am working on some code that inserts a piece of text and an id into a table. I send the code the text and the user's name. I want to take the username, and select the id that corresponds to it from the users table. I then insert that into the text table.

I have tried this:

$id = mysql_query("SELECT users.id FROM users WHERE name='$username'");

mysql_query("INSERT INTO `shouts` (`id`, `user`, `text`, `datetime`) VALUES (NULL, '$id', '$text', '$datetime');");

But it does not work, because the variable $id holds sql data. How can i turn that data into and integer?

What do you mean by it stores sql data? Could you add a sample of what's in there? — andrewsi
– andrewsi, Commented Jul 24, 2012 at 19:27

Jeff Lambert · Accepted Answer · 2012-07-24 19:40:32Z

3

From the manual:

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Also from the manual:

Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.

To answer your question, try this:

$id = mysql_query("SELECT users.id FROM users WHERE name='$username'");
$id = mysql_fetch_assoc($id);
mysql_query("INSERT INTO `shouts` (`id`, `user`, `text`, `datetime`) VALUES (NULL, '". $id['id']."', '$text', '$datetime');");

Although this is a Bad Idea™ and you should really look into using PDO. It's really not that hard. It will provide you with the ability to prepare your SQL statements, making you really be able to sleep easier at night knowing that you're not going to be a victim of SQL injection. The code you have is ambiguous at best whether or not you already are a victim yourself.

edited Jul 24, 2012 at 19:40

answered Jul 24, 2012 at 19:30

Jeff Lambert

24.7k4 gold badges72 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

See also A Guide to Preventing SQL Injection. Although it's good you've made a note, you really shouldn't post code here that's so recklessly vulnerable.

Robert · Accepted Answer · 2012-07-24 19:27:59Z

0

$id

is going to contain your results you need to get the id and put that into a variable. Check out the mysql_fetch_row function I think that will get you what you want. mysql_fetch_row syntax

answered Jul 24, 2012 at 19:27

Robert

3,0843 gold badges25 silver badges32 bronze badges

Comments

Joe Malt · Accepted Answer · 2012-07-24 19:28:36Z

0

mysql_query never returns a normal variable, only a result resource. Try mysql_fetch_assoc to get the data out of the select query. Also, the MySQL php extension is deprecated and you are encouraged to move to MySQLi or PDO.

answered Jul 24, 2012 at 19:28

Joe Malt

3971 silver badge16 bronze badges

Comments

mlishn · Accepted Answer · 2012-07-24 19:30:35Z

0

this should work

   $sql = mysql_query("SELECT users.id FROM users WHERE name='$username'");
    $id = mysql_fetch_array($sql)['id'];

answered Jul 24, 2012 at 19:30

mlishn

1,66714 silver badges19 bronze badges

Comments

Luke · Accepted Answer · 2012-07-25 06:40:40Z

0

try $id = addslashes($id); or any other method of encoding the sql string

edited Jul 25, 2012 at 6:40

Luke

11.5k43 gold badges63 silver badges70 bronze badges

answered Jul 24, 2012 at 19:27

Anze Jarni

1,1677 silver badges7 bronze badges

2 Comments

Anze Jarni Over a year ago

Oh, sorry. I was under the impression that your are trying to save a sql string to the table. Guys are right, first fetch the the results from the users table and then save it json encoded or serialized

Anze Jarni Over a year ago

If it is only one field you need from the users table, for example the id, then you don't need no serialization.

Collectives™ on Stack Overflow

SQL Insert value from array

5 Answers 5

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related