Print specific value returned from SELECT statement

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

57 Replies - 2730 Views - Last Post: 17 April 2016 - 08:54 AM Rate Topic: -----

#1 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Print specific value returned from SELECT statement

Posted 16 April 2016 - 10:13 AM

Hi Guys! Im working on a small website and im a little confused on one step. I'm following a very simple mvc pattern, ive currently got one controller that creates a session array that holds all the posted data IF the form has been submitted, This data is coming from several different dropdown boxes which all have different Ids:
if(isset($_POST['btn-calcCon'])){

$_SESSION['post-data'] = $_POST;
$_SESSION['post-data']['heatingType'];
$_SESSION['post-data']['meterType'];
$_SESSION['post-data']['noBedrooms'];
$_SESSION['post-data']['houseType'];
$_SESSION['post-data']['houseAge'];

}


I have a select statement in my Model class that uses these session variables in a WHERE clause as below:

<?php
session_start();
require_once('../Config/config.php');

class ConCalc 
{
    public $dbconn;

    public function __construct()
    {
        $database = new Database();
        $db = $database->dbConnection();
        $this->dbconn = $db;
    }

    public function getValues () {
    $stmt = $this->dbconn->prepare("SELECT Consumption FROM consumption WHERE HeatingType LIKE '" . $_SESSION['post-data']['heatingType'] . "' AND MeterType LIKE '" . $_SESSION['post-data']['meterType'] . "' AND Bedrooms LIKE '" . $_SESSION['post-data']['noBedrooms'] . "' AND HouseType LIKE '" . $_SESSION['post-data']['houseType'] . "' AND HouseAge LIKE '" . $_SESSION['post-data']['houseAge'] . "'");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);

    }
}

?>


Ive done a Var_Dump on the statement and it does print out the values that have been posted:
object(PDOStatement)[4]
  public 'queryString' => string 'SELECT Consumption FROM consumption WHERE HeatingType LIKE 'Gas' AND MeterType LIKE 'Standard' AND Bedrooms LIKE '1 or 2' AND HouseType LIKE 'Flat' AND HouseAge LIKE 'Less than 11''


Now the top two rows of my table are as follows:

HeatingType     MeterType   Bedrooms    HouseType   HouseAge              Consumption
Gas             Standard    1 or 2     Flat          Less than 11 years     5430
Gas             Standard    1 or 2     Flat          More than 11 years     7270


If i select Gas, Standard, 1 or 2, Flat, Less than 11 then the number 5430 should be displayed in a textbox once the form has been submitted.

Im a little unsure how to return that value. I tried to echo out my $stmt variable but it said PHP is unable to echo out a PDO object.

Any help will be appreciated Thanks!

This post has been edited by andrewsw: 16 April 2016 - 10:34 AM
Reason for edit:: removed PHP from topic title


Is This A Good Question/Topic? 0
  • +

Replies To: Print specific value returned from SELECT statement

#2 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6912
  • View blog
  • Posts: 28,592
  • Joined: 12-December 12

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 10:33 AM

You should be using prepared statements, not concatenating values to a string. See my signature below.

The docs for fetchAll confirm that it returns an array "containing all of the result set rows". For example,

Quote

Array
(
[0] => Array
(
[name] => pear
[0] => pear
[colour] => green
[1] => green
)

[1] => Array
(
[name] => watermelon
[0] => watermelon
[colour] => pink
[1] => pink
)

)

getValues() returns such an array, so you need to loop through the array examining its [Consumption] element (having first checked that the array contains something).

BTW It isn't a good idea to name a field the same as the table.

BTWW If you only need a single column then look at Example #2 Fetching all values of a single column from a result set in the linked docs-page.

I will also mention that if you are matching a value exactly then use equals = rather than LIKE.
Was This Post Helpful? 1
  • +
  • -

#3 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 10:41 AM

View Postandrewsw, on 16 April 2016 - 10:33 AM, said:

You should be using prepared statements, not concatenating values to a string. See my signature below.

I will also mention that if you are matching a value exactly then use equals = rather than LIKE.


Thank you for the reply!
I know how to do a prepared statements and binding things in an Insert statement but not 100% sure how to do it in a select statement.
I tried following Example #2 and did the following:

$sth = $stmt->fetchAll(PDO::FETCH_ASSOC);
		var_dump($sth);


And it showed that
array (size=0)
  empty

But im not sure why
Was This Post Helpful? 0
  • +
  • -

#4 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 11:12 AM

My Model now looks like this:

<?php
session_start();
require_once('../Config/config.php');

class ConCalc 
{
	public $dbconn;
	
	public function __construct()
	{
		$database = new Database();
		$db = $database->dbConnection();
		$this->dbconn = $db;

	}

	public function getValues () {



		$stmt = $this->dbconn->prepare("SELECT Consumption FROM consumption 
			WHERE HeatingType = :heatType 
			AND MeterType = :meterType 
			AND Bedrooms = :noBeds
			AND HouseType = :house 
			AND HouseAge = :age");

		$stmt->bindparam(":heatType", $_SESSION['post-data']['heatingType']);
		$stmt->bindparam(":meterType", $_SESSION['post-data']['meterType']);
		$stmt->bindparam(":noBeds", $_SESSION['post-data']['noBedrooms']);
		$stmt->bindparam(":house", $_SESSION['post-data']['houseType']);
		$stmt->bindparam(":age", $_SESSION['post-data']['houseAge']);
		$stmt->execute();
		var_dump($stmt);
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}
}

?>

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6912
  • View blog
  • Posts: 28,592
  • Joined: 12-December 12

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 11:20 AM

Dump the session data to confirm it holds the data you expect.

You've reverted to equals for all fields now, but you should still be using LIKE for the HouseAge, "Less than 11 years", that's why the array is empty.
Was This Post Helpful? 0
  • +
  • -

#6 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 11:23 AM

Right okay that seems to have printed out the values that are posted:

array (size=8)
  'gasUsed' => string 'Yes' (length=3)
  'heatingType' => &string 'Gas' (length=3)
  'meterType' => &string 'Standard' (length=8)
  'overnight' => string 'Yes' (length=3)
  'noBedrooms' => &string '1 or 2' (length=6)
  'houseType' => &string 'Terraced' (length=8)
  'houseAge' => &string 'Less than 11' (length=12)
  'btn-calcCon' => string 'Calculate' (length=9)

This post has been edited by andrewsw: 16 April 2016 - 11:29 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#7 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 11:43 AM

Still a little unsure on how to see the value that's returned from the select statement.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6912
  • View blog
  • Posts: 28,592
  • Joined: 12-December 12

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 11:47 AM

If you've corrected the HouseAge criteria then you can test it with the code you've used above:
$sth = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($sth);

Was This Post Helpful? 0
  • +
  • -

#9 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 11:50 AM

HouseAge criteria has been fixed
I tried:
$sth = $stmt->fetchAll(PDO::FETCH_ASSOC);
		var_dump($sth);


But all i got was:
array (size=0)
  empty



Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6912
  • View blog
  • Posts: 28,592
  • Joined: 12-December 12

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 12:00 PM

Is the HouseType Flat or Terraced?

When building and testing a statement start with the simplest version possible, returning a single field and using a single criteria. One you've proven that this base condition works, then add further criteria ;)
Was This Post Helpful? 0
  • +
  • -

#11 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 12:04 PM

In my database house type has 4 possible values: Flat, Terraced,Semi detached and Detached.
In the var_dump i posted earlier the one that i selected was terraced, similar to below the one that i selected was Flat:
array (size=8)
  'gasUsed' => string 'No' (length=2)
  'heatingType' => &string 'Electricity' (length=11)
  'meterType' => &string 'Standard' (length=8)
  'overnight' => string 'No' (length=2)
  'noBedrooms' => &string '1 or 2' (length=6)
  'houseType' => &string 'Flat' (length=4)
  'houseAge' => &string 'More than 11' (length=12)
  'btn-calcCon' => string 'Calculate' (length=9)

array (size=0)
  empty


Was This Post Helpful? 0
  • +
  • -

#12 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6912
  • View blog
  • Posts: 28,592
  • Joined: 12-December 12

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 12:11 PM

Progressive Testing

I encourage, as just mentioned, that you start with fewer criteria.

Otherwise, are you certain that something matches that set of criteria? Also check the source data for leading or trailing spaces, and spelling errors.

Also add this to the top of your code so that you can display all errors:
error_reporting(E_ALL);
ini_set('display_errors', '1');

Was This Post Helpful? 0
  • +
  • -

#13 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 12:15 PM

I narrowed it down to the values that are stored in the Database, That being HeatingType, MeterType, Bedrooms, HouseType and HouseAge. I used selected the exact values that i posted in my original post showing the top two rows of my table.
Var dumps showed the following:

array (size=6)
  'heatingType' => &string 'Gas' (length=3)
  'meterType' => &string 'Standard' (length=8)
  'noBedrooms' => &string '1 or 2' (length=6)
  'houseType' => &string 'Flat' (length=4)
  'houseAge' => &string 'Less than 11' (length=12)
  'btn-calcCon' => string 'Calculate' (length=9)

array (size=0)
  empty



Ah silly mistake! In my database i had "Less than 11 Years" however in my HTML i had "Less than 11" which meant it was looking for something that didnt exist. I'm now able to print out the Consumption Value:
array (size=7)
      'ConID' => string '1' (length=1)
      'HeatingType' => string 'Gas' (length=3)
      'MeterType' => string 'Standard' (length=8)
      'Bedrooms' => string '1 or 2' (length=6)
      'HouseType' => string 'Flat' (length=4)
      'HouseAge' => string 'Less than 11 years' (length=18)
      'Consumption' => string '5430' (length=4)


However im unsure as to how i would store the Consumption value in a variable and then display that in a textbox
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6912
  • View blog
  • Posts: 28,592
  • Joined: 12-December 12

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 12:26 PM

Quote

Ah silly mistake! In my database i had "Less than 11 Years" however in my HTML i had "Less than 11" which meant it was looking for something that didnt exist.

? That's what I pointed out earlier, with the use of LIKE. Anyway..

Look up arrays in the manual, here they are. Or find a tutorial. Knowing how to read, navigate and loop through arrays is fundamental knowledge.

Quote

..and then display that in a textbox

If you are only expecting a single value then you should check the size of the array to confirm this; then you can just read the first element of the array, and put its value in a textbox/some HTML element.
Was This Post Helpful? 0
  • +
  • -

#15 Bash915   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 16-April 16

Re: Print specific value returned from SELECT statement

Posted 16 April 2016 - 12:27 PM

Yes I just realised. I must have missed it. I saw that you posted it and highlighted it in bold. And thank you for the link. I'll have a look through the arrays and see what I can do!
Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »