1

my SQL statement is not outputting anything when run. Just an empty screen.

Here is my PHP code:

<?php
$con = mysqli_connect("localhost", "root", "root","payBills");
$paidBills = "SELECT * FROM houseBills WHERE houseID = '20'";
$resultset = mysqli_query($con, $paidBills);
$records = array();
//Loop through all our records and add them to our array
while ($r = mysqli_fetch_assoc($resultset)) {
    $records[] = $r;
}
//Output the data as JSON
echo json_encode($records);
?>

and here is my SQL tables

  CREATE TABLE `houseBills` (
  `houseBillID` int(11) NOT NULL AUTO_INCREMENT,
  `houseID` varchar(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `amount` varchar(10) NOT NULL,
  `date` varchar(50) NOT NULL,
  `addedBy` varchar(100) NOT NULL,
  PRIMARY KEY (`houseBillID`),
  UNIQUE KEY `houseBillID` (`houseBillID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `houseBills`
--

INSERT INTO `houseBills` (`houseBillID`, `houseID`, `name`, `amount`, `date`, `addedBy`) VALUES
(1, '20', 'Loo roll', '£10', '10', 'samstone920@googlemail.com'),
(2, '20', 'toothpaste', '3', 'egreg', '44tq');

Is there any plainly obvious that I am missing?

spogebob92
  • 1,474
  • 4
  • 23
  • 32
  • Use mysqli_error() to see if there is a sql error – John Conde Apr 04 '14 at 23:20
  • No errors. Its bizarre. – spogebob92 Apr 04 '14 at 23:21
  • If you run the query by hand it works? – Barmar Apr 04 '14 at 23:24
  • Yeah it does! This is most strange. I have other code very similar that is working fine. – spogebob92 Apr 04 '14 at 23:27
  • 1
    have your tried it without json_encode. – Mr. Radical Apr 04 '14 at 23:28
  • In the line of `mysqli_query` add the following to its end `or die(mysqli_error());` – SaidbakR Apr 04 '14 at 23:28
  • 1
    have you tried `echo var_dump($records)`? – Mr. Radical Apr 04 '14 at 23:29
  • The var_dump code works. Any ideas what the issue would be with using jsonencode? – spogebob92 Apr 04 '14 at 23:30
  • 1
    wrong charter type perhaps – Mr. Radical Apr 04 '14 at 23:32
  • any ideas for fixing it? – spogebob92 Apr 04 '14 at 23:33
  • 1
    No, but it is a common problem. See: http://stackoverflow.com/questions/1972006/json-encode-is-returning-null. You could try changing the CHARACTER to utf8 as suggested. – Mr. Radical Apr 04 '14 at 23:36
  • 1
    Your table is currently set as CHARSET=latin1 this should be changed into utf8. See this post: http://stackoverflow.com/questions/8906813/how-to-change-the-default-charset-of-a-mysql-table. `ALTER TABLE houseBills CONVERT TO CHARACTER SET utf8;` – Mr. Radical Apr 04 '14 at 23:40
  • @SamStone is your question solved? – Mr. Radical Apr 04 '14 at 23:42
  • It still not returning anything. (thanks for all this help btw) – spogebob92 Apr 04 '14 at 23:43
  • 1
    If you run `SHOW CREATE TABLE houseBills` in the MySQL commandline is the charset=utf8? In other words, has the content of your table changed into the correct format. – Mr. Radical Apr 04 '14 at 23:44
  • It won't let me execute that query. " Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available." But I have a unique column (just made one). – spogebob92 Apr 04 '14 at 23:47
  • 1
    Have you run the command `SHOW CREATE TABLE houseBills;` in the command line of MySQL in other words the DOS / UNIX like console. Black screen with white letters. – Mr. Radical Apr 04 '14 at 23:50
  • 1
    Or do you use phpMyAdmin? Or some similar program to create and adm your database? In case phpMyAdmin use the SQL command line. – Mr. Radical Apr 04 '14 at 23:52
  • I use phpMyAdmin. I'm inputing that Sql into the SQL section of the webpage, but its returning the error above. – spogebob92 Apr 04 '14 at 23:54
  • 1
    And the error you get is about a unique column that is strange. Wait a sec I will fire up my own local server. – Mr. Radical Apr 04 '14 at 23:56
  • Thanks, I really appreciate the help. This is very odd, and seems quite obscure. – spogebob92 Apr 04 '14 at 23:57
  • 1
    I like helping people out when they are stuck. I think the problem is the visual part of this command. You are now using a phpMyAdmin this is not the actual command line, but more a simpler GUI version. – Mr. Radical Apr 05 '14 at 00:00
  • Ive updated the main post to show what the SQL now is. Is says the charset is utf. – spogebob92 Apr 05 '14 at 00:01
  • 1
    Oke, so we have now ruled out the most common problem wrong encoding. B.T.W. is there a specific reason you want JSON_ENCODE? Perhaps for a jquery, etc. – Mr. Radical Apr 05 '14 at 00:03
  • Its for a an iOS app that will parse the JSON. – spogebob92 Apr 05 '14 at 00:05
  • 1
    Try adding this to your php page: `json_last_error_msg();`. Maybe, you will receive a good error message. Or `json_last_error()` when you are running PHP 5.3 - 5.5. – Mr. Radical Apr 05 '14 at 00:05
  • `houseID varchar(11) NOT NULL` as `INT` if house id is a number use the proper field type to represent that. If you keep using `varchar` for every type of field you will lose all the good functionality a database can offer you. `amount varchar(10) NOT NULL,` as `amount double(9,2) NOT NULL,` to represent currency and `date varchar(50) NOT NULL,` as `DATETIME`. – Prix Apr 05 '14 at 00:07
  • Doesn't give me anything at all unfortunately – spogebob92 Apr 05 '14 at 00:07
  • changed the houseID data type back to int. Still no results – spogebob92 Apr 05 '14 at 00:09
  • wait, its returned error 5! json_last_error() that is. – spogebob92 Apr 05 '14 at 00:10
  • 1
    Just to be sure you should echo out json_last_error_msg(). So like this: `echo json_last_error_msg();`. – Mr. Radical Apr 05 '14 at 00:10
  • [Here is a representation that your query and table are working](http://sqlfiddle.com/#!2/171e6/1/0), so make sure to always use the proper data types. – Prix Apr 05 '14 at 00:12
  • 1
    Oke, then it is still a encode problem. An answer on the past suggested that the data is still in another format. Have you filled the table with data and is it possible to test this by filling in some sample data in another table with the correct encoding from the start? – Mr. Radical Apr 05 '14 at 00:12
  • @Prix I appreciate the help but the data type isn't the issue here. The SQL statement. Its the JSON part thats giving the error. – spogebob92 Apr 05 '14 at 00:13
  • 1
    @Prix var_dump gave the correct output. Only problem is JSON_ENCODE. Do you have a idea? – Mr. Radical Apr 05 '14 at 00:13
  • @Mr.Radical - I love you. I just flushed out the data and re inputted it. I assume the previous data was a different charset as you said. Thank you so much! – spogebob92 Apr 05 '14 at 00:14
  • @SamStone I am aware the database is not the issue however you were not properly using data types as described on your question so I merely pointed out that you should always use the correct data type for the data you're filling it. **As for the `json_encode` could you post a sample of what you get and a sample of what it should give you?** – Prix Apr 05 '14 at 00:15
  • 1
    @SamStone good man. Would you mind if I post my solution below? – Mr. Radical Apr 05 '14 at 00:16
  • @mr.Radical If you want to form some sort of answer, I can mark you correct? – spogebob92 Apr 05 '14 at 00:16

1 Answers1

1

Table is currently set as CHARSET=latin1 JSON_ENCODE doesn't except this. See this post: json_encode is returning NULL?. ALTER TABLE houseBills CONVERT TO CHARACTER SET utf8;

Because the table already contains data before the alteration this stills give a problem. In this case (test phase project) the solution is to re-enter data. For large existing table perhaps try copying data to new table might offer a solution. Please note this is untested.

Community
  • 1
  • 1
Mr. Radical
  • 1,847
  • 1
  • 19
  • 29