getting error on php database query

Question

i don't know much about php. this is a php i'm using to send data form android. but while testing, i'm using it from a html. from this html i send iddevice, latitud and longitud variables.

as you can see in the php code, first i get the name related to the iddevice sent from the html from the DB.

the problem is I'm not getting the name variable from the database. the name should be "manuel", but on my mysql DB i'm getting "Resource id #2" instead. the other variables are correct in database row (iddevice, latitud and longitud).

any idea on why is this happening? thx in advance

php code:

<?php

// conexion a la base de datos
mysql_connect("xxx", "xxx", "xxx") or die (mysql_error());

mysql_select_db("qry899");

// conseguimos el nombre
$newname = mysql_query("SELECT nombre FROM gcm_v3 WHERE iddevice='".$_POST["iddevice"]."'") or die(mysql_error());

// insertamos id de registro devuelto por el GCM
mysql_query("INSERT INTO position (iddevice, nombre, latitud, longitud) VALUES ('".$_POST["iddevice"]."', '$newname', '".$_POST["latitud"]."', '".$_POST["longitud"]."')") or die(mysql_error());

mysql_close();

?>

`$newname` will hold the resource after the query is executed, you need to fetch the data, check mysql_fetch_* functions manual how to fetch data. — Abhik Chakraborty, Jun 03 '14 at 13:12
public website - use your common sense and don't post database login details... — Novocaine, Jun 03 '14 at 13:13
FYI, you are wide open to [SQL injections](http://stackoverflow.com/q/60174) and using an [obsolete API](http://bit.ly/phpmsql). — John Conde, Jun 03 '14 at 13:13
http://xkcd.com/327/ (though given you already posted your database credentials and they're still in the edit history I think that might be the least of your problems) — GordonM, Jun 03 '14 at 13:18
We need to come up with a 'cut-n-paste' comment about SQL injections and `mysql_` functions being deprectaed @JohnConde — Jay Blanchard, Jun 03 '14 at 13:22
@imsiso downvoters are not required to give comments for any number of reasons. — Jay Blanchard, Jun 03 '14 at 13:23
@JayBlanchard I have a few that I use depending on the context. — John Conde, Jun 03 '14 at 13:23
I already have one (more than one actually) lol @JayBlanchard (just as John does) ;-) (typing them over and over isn't our cup 'o tea lol) see below... — Funk Forty Niner, Jun 03 '14 at 13:23
Sidenote: Your present code is open to [**SQL injection**](http://stackoverflow.com/q/60174/). Use [`mysqli_*`](http://php.net/mysqli) functions. (which I recommend you use and with [**prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO**](http://php.net/pdo)) `mysql_*` functions are deprecated and will be removed from future PHP releases. — Funk Forty Niner, Jun 03 '14 at 13:24
@JayBlanchard - you know I said please and didn't say or I will KILL YOU and you know the reason will still help me to improve my question or understand and correct my mistake for next time.Any way thanks for your respond.(-: — Sina R., Jun 03 '14 at 13:26
But it isn't your question @imsiso, unless you changed identities from the question above to your answer below? The OP might have gotten DV's for using `mysql_` or for leaving the code open to SQL injection attacks or for including login information. *shrug* — Jay Blanchard, Jun 03 '14 at 13:30
@JayBlanchard - OK Sorry My English is not so good. You are right this is not my question. But This is what just happens on my questions too (down vote without reason). So what would happen if you let me know my mistakes. Maybe I will not make myself and others bother? — Sina R., Jun 03 '14 at 13:37
Rather than ask for DV reasons just ask how you can improve your question. — Jay Blanchard, Jun 03 '14 at 13:38
@JayBlanchard - that's what I do every time I got down vote. But also I myself will not wait for the OP to ask me . I will put something like `-1 please try too ...` or `please try to ...`. But still you can do as you wish. thanks again. — Sina R., Jun 03 '14 at 13:42

Sina R. · Answer 1 · 2014-06-03T13:30:13.543

3

You should use mysql_fetch_assoc($res) to get a row like:

+ Please avoid using mysql_ . Use mysqli or PDO

<?php

// conexion a la base de datos
mysql_connect("xxx", "yyyy", "zzz") or die (mysql_error());

mysql_select_db("ccc");

// conseguimos el nombre
$res1= mysql_query("SELECT nombre FROM gcm_v3 WHERE iddevice='".$_POST["iddevice"]."'") or die(mysql_error());//this will return a resource pointer

$record=mysql_fetch_assoc($res1);//this will return a record of pointed select the(res1)
$newname = $record['nombre'];// this will give you the specific field you were looking for 

// insertamos id de registro devuelto por el GCM
mysql_query("INSERT INTO position (iddevice, nombre, latitud, longitud) VALUES ('".$_POST["iddevice"]."', '$newname', '".$_POST["latitud"]."', '".$_POST["longitud"]."')") or die(mysql_error());

mysql_close();

?>

edited Jun 03 '14 at 13:30

answered Jun 03 '14 at 13:15

Sina R.

1,781
2
19
37

1

loop through the values will help. while ($row = mysql_fetch_assoc($res1)) { echo $row["nombre"]; } – Ramesh_D Jun 03 '14 at 13:23
*"Please avoid using mysql_ . Use mysqli or PDO"* --- Actually, without using prepared statements, both can still be open to SQL injection. – Funk Forty Niner Jun 03 '14 at 13:34
@Fred-ii- - yes But you know with links I had provided the reader will understand the benefits and the reason of using PDO or mysqli and also I am sure in that links there are somethings about SQL injection. – Sina R. Jun 03 '14 at 13:39
1

*Hm...* I'm not about to go through pages and pages of code to "hopefully" find something closely related to what you just said. The best thing to do is to simply mention the term "prepared statements", and the OP can Google it. – Funk Forty Niner Jun 03 '14 at 13:43
+1 - Thanks that's what I will do after this. I didn't know the keyword "prepared statements" – Sina R. Jun 03 '14 at 13:45
1

You're welcome. Here, save these two links in a text file, and/or add it to your answer: http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php and http://www.php.net/manual/en/pdo.prepared-statements.php – Funk Forty Niner Jun 03 '14 at 13:47

score 3 · Accepted Answer · answered Jun 03 '14 at 13:18

3

You are not actually extracting the value from the database, you need to use mysql_fetch_assoc:

// conseguimos el nombre
$result = mysql_query("SELECT nombre FROM gcm_v3 WHERE iddevice='".$_POST["iddevice"]."'") or die(mysql_error());
$row = mysql_fetch_assoc($result);
$newname = $row['nombre'];

And you should avoid mysql* functions, as mentioned by others.

Lastly change your database login details, they are in the edit history so anyone can access it

answered Jun 03 '14 at 13:18

Steve

20,703
5
41
67

1

*"Lastly change your database login details, they are in the edit history so anyone can access it"* --- Exactly. Yet, if anything and in order for that to not become accessible by others, the question would need to be deleted altogether. – Funk Forty Niner Jun 03 '14 at 13:30

score 0 · Answer 3 · answered Jun 03 '14 at 13:26

<?php
// conexion a la base de datos
mysql_connect("xxx", "xxx", "xxx") or die (mysql_error());

mysql_select_db("qry899");

// conseguimos el nombre
$newname = mysql_query("SELECT nombre FROM gcm_v3 WHERE iddevice='".$_POST["iddevice"]."'") or die(mysql_error());

$row = mysql_fetch_array($newname);
$newname1 = $row['latitud'];

// insertamos id de registro devuelto por el GCM
mysql_query("INSERT INTO position (iddevice, nombre, latitud, longitud) VALUES ('".$_POST["iddevice"]."', '$newname1', '".$_POST["latitud"]."', '".$_POST["longitud"]."')") or die(mysql_error());

mysql_close();

?>

score 0 · Answer 4 · answered Jun 03 '14 at 13:29

I will never work as long as u do it like this mysql_query("SELECT nombre FROM gcm_v3 WHERE iddevice='".$_POST["iddevice"]."'") try first to assign $_POST["iddevice"] to a variable and after use that variable in the query, something like this:

$device = $_POST["iddevice"];
mysql_query("SELECT nombre FROM gcm_v3 WHERE iddevice='$device'");

getting error on php database query

4 Answers4