Select query returning error

Question

I have a basic form where I am able to insert values into mysql db. I am having problems displaying the values previously inserted from the form. There are three tables in mysql db that share academy_id as a key. I am getting a php error for my SELECT query. What is the best way to properly display values from several table sharing a foreing key? SITE

if(isset($_POST['submit'])) {


$db_insert  = $db_con->prepare("INSERT INTO academy (name, type, status, academy_id, street_address, zipcode, city, state, comments) VALUES (?,?,?,?,?,?,?,?,?)"); 
$db_insert->bind_param('sssisssss', $_POST['name'], $_POST['type'], $_POST['status'], $_POST['acad_id'], $_POST['street'], $_POST['zipcode'], $_POST['city'], $_POST['state'], $_POST['acad_comm']);
$db_insert->execute();

$academy_id = $_POST['acad_id'];



 //Query to Read Values from database based on academy_id 
$db_select  = $db_con->prepare("
SELECT a.name AS 'Academy Name:', 
       a.academy_id AS 'Academy ID:',
       a.status AS 'STATUS:',
       a.type AS 'Type:',
       a.street_address AS 'Street:',
       a.city AS 'City:',
       a.state AS 'State:',
       a.zipcode AS 'Zip Code:',
       a.comments AS 'Comments:',
       c.course_name AS 'Courses Name:',
       ac.start_date AS 'Course Start Date:',
FROM academy a
WHERE academy_id = $academy_id
INNER JOIN courses_by_academy ac ON a.id = ac.academy_id
INNER JOIN courses_selection_list c ON c.id = ac.course_id
");
$db_select->bind_param("i", $academy_id);
$db_select->execute();
}

?>
<form action="test9.php" method="POST">
        Name: <input type="text" name="name"></br> 
        Academy Status:
        <select>
          <option value="ACTIVE">ACTIVE</option>
          <option value="INACTIVE">INACTIVE</option>
        </select>
        Type:
        <select>
          <option value="Upper-Secondary">Upper-Secondary</option>
          <option value="Post-Secondary">POST SECONDARY</option>
        </select>

        Courses being offered?
        <select name="courses_offered">
            <option value="1">1</option>
            <option value="2">2</option>
        </select>
        <div id="course_catalog"></div>
        Academy ID: <input type="text" id="acad_id" name="acad_id"></br>
        Address: <input type="text" id="street" name="street"></br>
        Zip Code: <input type="text" id="zip_input" name="zipcode"></br>
        City: <input type="text" id="city" name="city" value=""></br>
        State: <input type="text" id="state" name="state" value=""></br>
        Overall Notes/Comments:</br><textarea id="acad_comm" name="acad_comm" rows="4" cols="50"></textarea></br>

    <input value="SAVE" name="submit" type="submit">
</form>

Remove the extra comma just before the `FROM` and move the `WHERE` to the end. — DevlshOne, Nov 18 '13 at 23:06
Follow @DevlshOne advice and check out: http://stackoverflow.com/questions/4488035/call-to-a-member-function-bind-param-on-a-non-object — jk., Nov 18 '13 at 23:08
For reference, here's the error I see: `Fatal error: Call to a member function bind_param() on a non-object in /home/vhosts/webprolearner.ueuo.com/test/test9.php on line 85` — jk., Nov 18 '13 at 23:19

Ryan J · Accepted Answer · 2013-11-19T00:56:38.740

I believe you need to structure your joins before the WHERE clause. It also seems you may be getting an ambiguous field error, as you mention "academy_id" in your WHERE clause, yet you don't specify which table you're checking. I believe you'll get a MySQL engine error when you try it this way.

//Query to Read Values from database based on academy_id 
$db_select  = $db_con->prepare("
SELECT a.name AS 'Academy Name:', 
       a.academy_id AS 'Academy ID:',
       a.status AS 'STATUS:',
       a.type AS 'Type:',
       a.street_address AS 'Street:',
       a.city AS 'City:',
       a.state AS 'State:',
       a.zipcode AS 'Zip Code:',
       a.comments AS 'Comments:',
       c.course_name AS 'Courses Name:',
       ac.start_date AS 'Course Start Date:'
FROM academy a
INNER JOIN courses_by_academy ac ON a.id = ac.academy_id
INNER JOIN courses_selection_list c ON c.id = ac.course_id
WHERE a.academy_id = $academy_id
");

Also, the error isn't posted here, so it's hard to guess what your problem is.

Maybe but also this error `ac.start_date AS 'Course Start Date:', FROM` that @DevIshOne already pointed out. That comma before `FROM` shouldn't be there. — jk., Nov 18 '13 at 23:11

score 1 · Answer 2 · answered Nov 18 '13 at 23:13

Well I closed your site when that suspicious as hell media player update box came up... so I can't say for sure, but I can see that you aren't doing anything with the results you are getting back from the DB. The PHP error you mention must be something different.

You need something like

foreach ($db_select->fetchAll() as $row)
  {
      echo $row['name'].", ".$row['academy_id']; //do whatever with code
  }

Also, you should always put your SQL statements in a try catch block,

 try{
$db_select  = $db_con->prepare("
...

}catch(PDOException $e) {
   echo $e->getMessage();
}

Select query returning error

2 Answers2