Help with sql and strings

Question

this is my mysql query:

    mysql_query("INSERT INTO Coordenadas (ID, LAT, LNG, DESCRICAO) VALUES ($id, $coords[1], $coords[0], $string_cortada)");

when I echo $id, $coords[0], $coords[1] and $string_cortada with this:

echo '</br>$id:'.$id.'</br>coordenadas:'.$coords[1].','.$coords[0].'</br>string: ' . $string_cortada;):

this is what I get:

$id: 1
coordenadas:-22.9028102,-43.2498512
string: marechal rondon 

$id: 2
coordenadas:-22.9824202,-43.3605722
string: rua joana angelica 

$id: 3
coordenadas:-22.8866218,-43.3699090
string: passarela barra

working data, but when I do:

mysql> select * from coordenadas;

this is the output:

+----+------+-------------+-------------+-----------+------+
| ID | TIPO | LAT         | LNG         | DESCRICAO | DATA |
+----+------+-------------+-------------+-----------+------+
|  2 | NULL | -22.8544942 | -43.5350719 | 1         | NULL |
+----+------+-------------+-------------+-----------+------+

DESCRICAO didn't work, TIPO didn't work, only LAT and LNG, I can't find why !!

this is coordenadas table description:

mysql> desc coordenadas;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | int(11)      | NO   | PRI | NULL    | auto_increment |
| TIPO      | varchar(15)  | YES  |     | NULL    |                |
| LAT       | double       | NO   |     | NULL    |                |
| LNG       | double       | NO   |     | NULL    |                |
| DESCRICAO | varchar(150) | YES  |     | NULL    |                |
| DATA      | date         | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

thanks

Notice that he numbers (double) work but the strings (varchar) don't. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 5, 2011 at 13:31

GordyD · Accepted Answer · 2011-06-19 22:28:08Z

4

You have an error in your SQL query:

 mysql_query("INSERT INTO Coordenadas (ID, LAT, LNG, DESCRICAO) VALUES ($id, $coords[1], $coords[0], $string_cortada)");

$string_cortada needs to be encapsulated with speech marks as it is a string. Change it to:

 mysql_query("INSERT INTO Coordenadas (ID, LAT, LNG, DESCRICAO) VALUES ($id, $coords[1], $coords[0], '$string_cortada')");

For future debugging I would write SQL queries in PHP like so:

$sql = "INSERT INTO Coordenadas (ID, LAT, LNG, DESCRICAO) VALUES ($id, $coords[1],        $coords[0], '$string_cortada')"

$result = mysql_query($query) 

if (!$result) {
 $message  = 'Invalid query: ' . mysql_error() . "\n";
 $message .= 'Whole query: ' . $query;
 die($message);
}

This way you can see what the query sent to your database looks like and also see the cause of the error when an error occurs. This should help you quickly identify any future formatting errors.

To remove white space surrounding (at the beginning and end) a string you can use the trim() method as so:

$text = trim($text);

To remove all whitespace from a string you can use preg_replace like so:

$text = preg_replace('/ /', '', $text);

edited Jun 19, 2011 at 22:28

answered May 5, 2011 at 13:31

GordyD

5,10328 silver badges30 bronze badges

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

1 Comment

GordyD Over a year ago

Added how to remove whitespace from a string.

Syjin · Accepted Answer · 2011-05-05 13:32:49Z

1

You didn't specify TIPO in your input-Query... And for DESCRICAO try using singlequotes like

VALUES ($id, $coords[1], $coords[0], '$string_cortada')

answered May 5, 2011 at 13:32

Syjin

2,8091 gold badge30 silver badges31 bronze badges

Collectives™ on Stack Overflow

Help with sql and strings

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related