1

I am trying to develop an upcoming course dates for my website. I want to maintain the data in an mysql database. I have set up 3 table in msql.

1.courses

2.category

3.coursedates

All are linked by course_id.

Basically I want to present the data from coursedates in the following way in PHP using a query.

Course Title          No Of Days            Course Date

I have tried using the follwoing coding 

<?php
$con=mysqli_connect("localhost","root","","mentertraining");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


$query = "SELECT `coursedates`.`coursedate_id`,`courses`.`course_title`,`courses`.`no_of_days`,`category`.`category_name`,`coursedates`.`date1` FROM coursedates\n"
    . "LEFT JOIN `mentertraining`.`courses` ON `coursedates`.`course_id` = `courses`.`course_id` \n"
    . "LEFT JOIN `mentertraining`.`category` ON `courses`.`category_id` = `category`.`category_id` LIMIT 0, 30 ";

$result = mysql_query($query);
    echo "<table border='1'>
<tr>
<th>Course Title</th>
<th>Course Date</th>

</tr>";

while($row    = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>" . $row['course_title'] . "</td>";
  echo "<td>" . $row['date1'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>
Improve this question
2
  • 2
    ...and what happened? Commented Jun 30, 2013 at 9:03
  • I get this error when I use the above code. Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\mentertraining\pp.php on line 22 Commented Jun 30, 2013 at 9:09

3 Answers 3

Reset to default
2

Replace your code with the following :

<?php
$con=mysqli_connect("localhost","root","","mentertraining");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


$query = "SELECT `coursedates`.`coursedate_id`,`courses`.`course_title`,`courses`.`no_of_days`,`category`.`category_name`,`coursedates`.`date1` FROM coursedates "
    . " LEFT JOIN `mentertraining`.`courses` ON `coursedates`.`course_id` = `courses`.`course_id` "
    . " LEFT JOIN `mentertraining`.`category` ON `courses`.`category_id` = `category`.`category_id` LIMIT 0, 30 ";

$result = mysqli_query($con,$query);
    echo "<table border='1'><tr><th>Course Title</th><th>Course Date</th></tr>";

while($row    = mysqli_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>" . $row['course_title'] . "</td>";
  echo "<td>" . $row['date1'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for that. just run it and still getting these 2 errors. Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\xampp\htdocs\mentertraining\trial.php on line 14 Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\mentertraining\trial.php on line 17
try again and using mysqli_query($con,$query);
1

You are mixing mysql_* and mysqli:

$con=mysqli_connect("localhost","root","","mentertraining");

But:

$result = mysql_query($query);//?

Use mysqli_query instead:

$query = "SELECT 
       `coursedates`.`coursedate_id`,
       `courses`.`course_title`,
       `courses`.`no_of_days`,
       `category`.`category_name`,
       `coursedates`.`date1` 
FROM
    coursedates
LEFT JOIN
      `mentertraining`.`courses` 
ON 
   `coursedates`.`course_id` = `courses`.`course_id`
LEFT JOIN
     `mentertraining`.`category` 
ON 
   `courses`.`category_id` = `category`.`category_id`
LIMIT 0, 30";

$result = mysqli_query($query);
echo "<table border='1'>
<tr>
<th>Course Title</th>
<th>Course Date</th>

</tr>";

/* fetch associative array */
while ($row = mysqli_fetch_assoc($result)) {
    echo "<tr>";
    echo "<td>" . $row['course_title'] . "</td>";
    echo "<td>" . $row['date1'] . "</td>";
    echo "</tr>";
}

/* free result set */
mysqli_free_result($result);

echo "</table>";

mysqli_close($con);
?>
Improve this answer

5 Comments

And mysqli_fetch_assoc().
Sorry guys bit of a newbie. Ive just changed the mysql to mysqli but still getting these errors. Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\xampp\htdocs\mentertraining\pp.php on line 14 Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\mentertraining\pp.php on line 22
Hi user4035. That as sorted out the first error still getting this one though Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\mentertraining\trial.php on line 17
@user2536134 It means, that your SQL query is incorrect and mysqli_query returns false
@user2536134 I updated my answer, so all the query is displayed on the screen, but there is no difference with your query. Check it carefully in mysql console. I can't tell you what's wrong with it without seeing the sample data and tables structures.
0

Note that your query might be more legible rewritten something like this...

"
SELECT cd.coursedate_id
     , c.course_title
     , c.no_of_days
     , t.category_name
     , cd.date1 
  FROM coursedates cd
  LEFT 
  JOIN courses c
    ON c.course_id = cd.course_id 
  LEFT 
  JOIN category t
    ON t.category_id = c.category_id 
 LIMIT 0, 30;
 "

Incidentally, it seems odd that you could have course dates for a course that didn't exist, so that first OUTER JOIN could probably be rewritten as an INNER JOIN!

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.