CODEWITHSUNDEEP
phpmysql
Zander Fields
Zander Fields

Reputation: 41

Trying to echo a single row with multiple columns from database

I'm at the end of the user process, and I can't seem to echo all of the data in a single row with 10 columns. Here's my code:

$state = $_POST['state'];

$city = $_POST['city'];

$jobTitle = $_POST['jobTitle'];



try {
$db = new PDO("mysql:host=localhost;dbname=TeacherData;port=8889","censored","censored");
}   catch (Exception $e) {
echo "Could not connect to database.";
exit;
}




$sql = "SELECT
TOT_EMP, 
H_MEAN, 
JOBS_1000,
A_MEAN,
H_PCT10,
H_MEDIAN,
H_PCT90,
A_PCT10,
A_MEDIAN,
A_PCT90
FROM teacherdataall 
WHERE PRIM_STATE='" . $state . "' 
AND AREA_NAME='" . $city . "' 
AND OCC_TITLE='" . $jobTitle . "'";

$result = $db->query($sql);

if($result !== false) 
{
    $cols = $result->columnCount();           // Number of returned columns

    echo 'Number of returned columns: '. $cols. '<br />';

    foreach($result as $row) 
        {
          echo $row['TOT_EMP'] . ' - ' . $row['H_MEAN'] . ' - '. $row['JOBS_1000'] . ' - ' . $row['A_MEAN'] . ' - '. $row['H_PCT10'] . ' - ' . $row['H_MEDIAN'] . ' - '. $row['H_PCT90'] . $row['A_PCT10'] . ' - ' . $row['A_MEDIAN'] . ' - ' . $row['A_PCT90'] . '<br />';
        }
}

No matter what variation of the print/echo function, I can't get the data to appear! It's only one row!! Any help would be very much appreciated.

Here's the code from page 3:

<?php
$state = $_POST['state'];

$city = $_POST['city'];

try {
$db = new PDO("mysql:host=localhost;dbname=TeacherData;port=8889","root","root");
}       catch (Exception $e) {
    echo "Could not connect to database.";
    exit;
}

$stmt = $db->prepare("SELECT DISTINCT OCC_TITLE FROM teacherdataall WHERE PRIM_STATE='" . $state . "' AND AREA_NAME='" . $city . "';");


echo "<br>";
echo "<form action='fourth_page.php' method='post'>";
echo "State: ";
echo "<select name='state'><option value=$state>$state</option>";
echo "</select>";
echo "City: ";
echo "<select name='city'><option value=$city>$city</option>";
echo "</select>";
echo "<select name='jobTitle'><option value=''>-- Job Title --</option>";

if ($stmt->execute()) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) 
    {
        echo '<option value="' . $row['OCC_TITLE'] . '">' . $row['OCC_TITLE'] . '</option>';
        echo "<hr>";
    }
echo "</select>";
echo "<button type='submit'>Next</button>";
}
    echo "</form>"; 



?>

Completion edit: I was getting a bad value passed from $_POST due to not adding single quotes around the html value=$city. Everything is fixed now! Thanks @Ray!

Upvotes: 1

Views: 2721

Answers (2)

ohgoodnessdude
ohgoodnessdude

Reputation: 23

I was currently looking for this and it was quite difficult to do the most liked option above. I found someone who posted a similar question and it worked for me to get a dropdown list of the sql.

I hope this helps anyone looking for a slightly different answer.

This is the code i used and on the first try my problem was fixed.

Upvotes: 0

Ray
Ray

Reputation: 41448

colCount() tells you number of columns requested not the results your getting back. You probalby want rowCount() as you allready know how many columns you've explicitly set in your query. In your case your select statement has: TOT_EMP, H_MEAN, JOBS_1000, A_MEAN, H_PCT10,H_MEDIAN, H_PCT90, A_PCT10, A_MEDIAN, A_PCT90. This means you're always going to get back then number 10 for number columns you specify in your SQL query.

All that being said, I suspect your query is running successfully, but returning an empty set of results (no matches). Try running the query you build in $sql manually.

You also need to fetch the rows from the result if you want to treat it as an associative array:

 $rowCount = $result->rowCount(); 

 echo 'Number of returned results: '. $rowCount. '<br />'; 

 $rows = $result->fetchAll(PDO::FETCH_ASSOC);


  foreach($rows as $row) 
  {
       //... rest here

My final note is you should not be using $_POST array data directly in your query unless you really want SQL injection. Use prepared statement and bind, bind, bind your way to happiness:

$sql = "SELECT TOT_EMP, H_MEAN, JOBS_1000,A_MEAN,H_PCT10, H_MEDIAN,H_PCT90, A_PCT10,
        A_MEDIAN, A_PCT90 FROM teacherdataall  WHERE PRIM_STATE=:state AND    
       AREA_NAME=:area  AND OCC_TITLE=:jobTitle";


 $area = "$city, $state";
 $statement = $db->prepare($sql);
 $statement->bindValue(":state", $state);
 $statement->bindValue(":area", $area);
 $statement->bindValue(":jobTitle", $jobTitle);

 $result = $statement->execute();

Upvotes: 3

Related Questions