I created a web service in php and which returns JSON. It works fine but it is not consistent most of the times it pulls all the information needed other times it leaves out a specific column which is the second to last column named table_bio it says its null but there is data in the column when you check it via phpmyadmin. I thought it was a caching issue however I put in the relevant headers in apache and within the service and still no luck. Thing is that the service is getting the correct information but not returning the correct info.
Below is my code:
<?php
error_reporting (E_ALL ^ E_NOTICE);
$id = $_GET['Table'];
/* grab the posts from the db */
$query = "SELECT * FROM table WHERE table_id='$id'";
$result = mysql_query($query,$link) or die('Errant query: '.$query);
/* create one master array of the records */
while($post = mysql_fetch_assoc($result)) { $posts = $post; }
/* output in necessary format */
header('Cache-Control: no-cache, no-store, must-revalidate'); // HTTP 1.1.
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Pragma: no-cache'); // HTTP 1.0.
header('Expires: 0'); // Proxies.
header('Content-type: application/json');
echo $_GET['onJSONPLoad'];
echo "(" . json_encode($posts) . ")";
@mysql_close($link);
?>
Your code doesn’t load all the posts, but rather first loads all of them, and then overrides the previous one all the time, so you end up with just one post. Which one that is depends on the natural ordering of MySQL, since you haven’t defined any ordering yourself.
i.e., this:
<?php
/* create one master array of the records */
while($post = mysql_fetch_assoc($result)) { $posts = $post; }
should be this:
/* create one master array of the records */
$posts = array();
while($post = mysql_fetch_assoc($result))
{
$posts[] = $post;
}
Also
mysql_* is deprecated, switch to mysqli_ or PDO (latter is preferred)
your code is vulnerable to SQL injection. Once using mysqli_* or PDO, use prepared statements
Do not use error_reporting E_ALL ^ E_NOTICE. Notices are there for a reason, and they really help in spotting errors you can waste hours on finding (mostly typos in variable namess). Use error_reporting(-1) to show any and all types of errors.
I am only returning 1 result so I dont need the full array
Also i know my code has security issues however this is just a test I will convert to PDO and such later
Lastly the error I am getting is that I am getting a result but not the whole result for example if the table has 3 columns. I get a result with 3 columns but only 2 has data in them one column is coming as null and within the database I can see the column has data in it
Is this true for all rows in the table? As I said in my previous post, your code will return one random row from the table, since you don’t tell MySQL how to order the results.
Also, if you want just one row, you should ask MySQL for one row, not ask it for all rows and discard all but the last. This takes more time in MySQL and in PHP than just asking one row would
<?php
$res = mysql_query('SELECT something,anything,just,not,dreaded,star FROM daTable WHERE something="somethingelse" ORDER BY something LIMIT 1');
$row = mysql_fetch_assoc($res); // no need for while(), since there only is one (or zero, of no row meets the criteria) result due to LIMIT 1
Why not do it right from the start, and prevent yourself the pain from having the refactor a whole lot of code later?
check my sql statement I am asking for one row not all. I am asking for all the columns within a particular row. The reason for doing it this way at first is because this is the way i know when doing the pdos it is something new to mean so if I know i have working code then I can follow tutorials to have it more secure.
Ah, so the value of table_id is unique then? Yes, in that case you don’t need the LIMIT 1 (although it’s still advisable to include it anyway)
Now that that’s clear, I don’t see any reason why your code shouldn’t work …
After you’ve set all the data in $posts, what do you see when you var_dump($posts); ?
I found my solution however it brought about another problem the solution is that I need to encode my json response because of the datatype that particular column is so I have done this
$posts[] = array_map('utf8_encode',$post);
. However now I am getting the entire dump of the the row but for some reason my callback is now not working. My code is below
jQuery
var durl = "a valid url";
$.ajax({
type: "GET",
url: durl,
data: "Pass="+avalidid,
dataType: "jsonp",
cache : false,
jsonp : "onJSONPLoad",
jsonpCallback: "callback",
contentType: "application/json",
crossDomain: "true",
}).done(function(){
}).fail(function(){
$.mobile.changePage("errorpage");
})
});
//callback function
function callback(rtndata)
{
alert(rtndata.name);
}