• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
can't write strings to database

#1
[eluser]illuzionist[/eluser]
Hello everybody,

so I'm trying to write a string to the database and this is what I do..

I have a url just like this:
www.example.com/index.php/site/writeGPStoDatabase/?lat=45.7071587&lng=16.0575276

now what I do is I have a controller "site" and method inside called "writeGPStoDatabase", and from this method I try to write to database like this, but it won't work:

this is the code:
Code:
$locationGPS = $this->uri->segment(3);      
        
$query = 'UPDATE myTable SET time_D=current_time' . ',locationGPS=' . $locationGPS . ' WHERE id=' . $id;
$this->db->query($query);
and this code just won't work, if I change $locationGPS and put some other string it also doesn't work, it only works if I put in number values like "22002", "1113423" etc..

in SQL database locationGPS column is set to varchar(255)

TL;DR;
CI won't write strings to database if they contain letters, only if string is made up of numbers ONLY

#2
[eluser]CroNiX[/eluser]
Don't strings need to be quoted going into the database? Yours isn't. Numbers don't need to be, so that's why they work ok.

You're also not escaping your variables, which is not good and leaves you open to SQL injection. Always escape variables on manual query, or better yet, use Active Record or query bindings which escapes all variables automatically. A hacker could easily change your query string, just sitting there openly in the url, and enter some SQL commands there and your code would execute it blindly and they could take your site over, get a list of users/passwords, or whatever. Not good.

#3
[eluser]illuzionist[/eluser]
thanks man, I will try to use active records, I will tell you how it went Smile thanks again

#4
[eluser]illuzionist[/eluser]
ok now I did this and it just writes 0 to my locationGPS column, code is like this

Code:
$locationGPS = $this->uri->segment(3);
$data = array(
            'locationGPS' => $locationGPS
        );
$this->db->where('id', $id);
        $this->db->update('myTable', $data);

any thoughts on what might be wrong?

#5
[eluser]CroNiX[/eluser]
Try echoing out $locationGPS before inserting and see if it's correct.

also, try running:
Code:
echo $this->db->last_query();
right after your query to see exactly what it used.

#6
[eluser]illuzionist[/eluser]
there is something wrong with trying to cast those numbers to string..
now when I have url like this:
www.example.com/index.php/site/writeGPStoDatabase/?lat=45.7071587&lng=16.0575276

in writeGPStoDatabase method I do this:

Code:
$latitude = $_GET["lat"];
$longitude = $_GET["lng"];

$locationGPS = "Latitude: " + (string) $latitude + ", Longitude: " + (string) $longitude;

and when I echo out $locationGPS I get 61.7646863 which is actually those 2 numbers added together instead "Latitude: 45.7071587, Longitude: 16.0575276"
this realy confuses me, I thought I casted those values all to (string) when I created $locationGPS variable..


EDIT
omg what an obvious mistake that has been bothering me.. I'm juggling between javascript and php atm and I used javascript syntax to concat strings and used "+" character.. and in PHP you must use dot "." to concat strings.. unbelievable..

#7
[eluser]CroNiX[/eluser]
Yes you cast them, but then you tried to add them together as if they were numbers.
+ is addition, as in math.
. is string concatenation, as in joining strings together

Change your +'s to .'s

Not sure what you are doing with the gps coordinates, but personally I would save each value in their own lat/lng column (decimal), or I would just save them as a lat/lng pair separated by a comma, like "45.7071587,16.0575276" and then split them by the "," when retrieving. That would allow you to use the gps data for anything without having to change formats (since you are storing the coordinates in a non-standard format along with the text "Latitude" and "Longitude", it will be difficult to use them for anything else without more manipulation...)

#8
[eluser]illuzionist[/eluser]
[quote author="CroNiX" date="1342294996"]Yes you cast them, but then you tried to add them together as if they were numbers.
+ is addition, as in math.
. is string concatenation, as in joining strings together

Change your +'s to .'s

Not sure what you are doing with the gps coordinates, but personally I would save each value in their own lat/lng column (decimal), or I would just save them as a lat/lng pair separated by a comma, like "45.7071587,16.0575276" and then split them by the "," when retrieving. That would allow you to use the gps data for anything without having to change formats (since you are storing the coordinates in a non-standard format along with the text "Latitude" and "Longitude", it will be difficult to use them for anything else without more manipulation...)[/quote]

yeah I know, it would be better.. but I didn't make this SQL database, I just have a task to do upgrade some new functionality on the stuff that already exists Sad

#9
[eluser]CroNiX[/eluser]
I'd bring up how inefficient it is to store "latitude" and "longitude" over and over and over in the database and maybe you can get more hours out of the project Smile

#10
[eluser]illuzionist[/eluser]
I will bring that up from now on Smile thanks


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.