Database Information Not Outputting

@Joseph_WitchardNov 28.2008

I did a var_dump on $stmt, and this is what I got:

[code=php]bool(false) [/code]

The rest of my code:

[code=php]<?php

require_once(“path_to_connection”);

$conn = connection_settings;

$query = “SELECT `author_id`, `author_name`, DATE_FORMAT(`date_posted`, ‘%W, %M %d %l:%i %p’) `category_id`, `categories.category_name`, `title`, `post_body` FROM `posts` JOIN `categories` ON `posts.category_id` = `categories.category_id` LIMIT 1”;

?>

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”> <html>

<head>

<title>Rebirth Test Page</title>

<meta name=”keywords” content=”harry potter,hogwarts,hogwarts rpg,hogwarts online,jk rowling”> <meta http-equiv=”content-type” content=”text/html; charset=utf-8″> <meta http-equiv=”cache-control” content=”no-cache”>

<link href=”/css/test.css” rel=”stylesheet” type=”text/css”>

</head>

<body>

<div id=”wrapper”>

<div id=”headers”>

<div id=”header1″></div>

<div id=”header2″>

</div>

<div id=”navigation”>

<!– Begin Navigation Menu –>

<?php // include the navigation menu

include(“../includes/navigation2.html”); ?>

<!– End Navigation Menu –>

</div>

<div id=”right”>

<p>test text</p>

</div>

<div align=”center” id=”center”>

<div id=”welcome”>

<p>welcome text</p>

<table>

<?php

if ($stmt = $conn->prepare($query)) {

$stmt->bind_result($authorID, $author, $date, $catID, $category, $title, $body);

$stmt->execute();

while ($stmt->fetch()) {

echo “<tr><td><b>” . $title . “</b></td></tr>”; echo “<tr><td><b>Posted By: ” . $author . “on ” . $date . “in ” . $category . “</b></td></tr>”; echo “<tr><td>” . $body . “</td></tr>”; } $stmt->close(); } $conn->close();

var_dump($stmt); ?>

</table>

</div>

</body>

</html>[/code]

For some reason, no data is being output. Does anyone have an explanation?

to post a comment

PHP

20 Comments(s) _↴

@MindzaiNov 28.2008 — #What's in your connection include (minus sensitive data of course)?

@chazzyNov 29.2008 — #it's simply because you're using mysqli without error checking. just because it's different doesn't mean you still don't have to do error checking.

[code=php]
  $stmt->bind_result($authorID, $author, $date, $catID, $category, $title, $body);
 
  $stmt->execute();
 [/code]

I believe the error's in your sql (which surprisingly looks like sql..) around here:

<i>
 </i>DATE_FORMAT(<span><code>date_posted</code></span>, '%W, %M %d %l:%i %p') <span><code>category_id</code></span>,

I think you want a comma.

@Joseph_WitchardauthorNov 29.2008 — #Did the comma, but nothing is being output (still). I did var_dump again, and $stmt is still coming out as false.

And what do you mean I'm not doing error checking? I thought I was doing everything right? (good coding practices, I mean)

@chazzyNov 29.2008 — #based on the placement of

[code=php]var_dump($stmt);[/code] it's completely expected that you get false, as you've called $stmt->close() already. have you verified that data is actually supposed to be reported, by using a plain sql tool (like php my admin?) and see that the query returns data?

both bind and execute return false on failure, so you might want to check that binding and/or executing isn't failing.

i believe your error's that you're binding before executing, rather than binding after executing (which is the correct way).

@Joseph_WitchardauthorNov 30.2008 — #Tried all of that, and nothing is working yet. So, I went into the table in phpMyAdmin and ran a query. An error was returned, along with this message:

Column 'category_id' in field list is ambiguous [/QUOTE]

I don't know what that means?

@chazzyNov 30.2008 — #Tried all of that, and nothing is working yet. So, I went into the table in phpMyAdmin and ran a query. An error was returned, along with this message:

I don't know what that means?[/QUOTE]

It means that both tables in your join have the same column, category_id, so you need to use a prefix from one of the tables for it to determine which to use.

@Joseph_WitchardauthorNov 30.2008 — #What do you mean? Could you give an example?

@chazzyNov 30.2008 — #

<i>
 </i>select col_a, col_b, a.col_c from table_a a, table_b b where a.col_c = b.col_c

you would use the same syntax regardless of whether it's mysqli or not. in this case, table_a was given the alias a, and table_b was given the alias b. since it's implied that col_c is in both tables, we use table_a's alias to determine which should be returned.

@Joseph_WitchardauthorNov 30.2008 — #Why exactly is there an a after table_a, and a b after table_b? What is it, and what purpose does it serve?

@chazzyNov 30.2008 — #table_a was given the alias a, and table_b was given the alias b[/QUOTE]

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

@Joseph_WitchardauthorNov 30.2008 — #I'm well aware that you're probably at the point to where you want to rip your teeth out rather than deal with me again, but I have no idea what that page meant:o Didn't understand it at all.

@chazzyNov 30.2008 — #you understand what an alias is right? My real name is John but I go by the alias "chazzy." Similarly, your name appears to be Joseph, so people might give you the alias "Joe," "Joey," or even "Jeff."

Similarly, tables and columns in sql can be given aliases, another name in which to reference them. let's take your famous mysqli incorrect format message.

<i>
 </i>SELECT <span><code>user_id</code></span>, <span><code>username</code></span> FROM <span><code>users</code></span> WHERE <span><code>user_id</code></span> = ? AND <span><code>username</code></span> = ?

Can be rewritten as

<i>
 </i>SELECT a.user_id, a.username FROM users a WHERE a.user_id = ? AND a.username = ?

which is functionally equivalent to

<i>
 </i>SELECT users.user_id, users.username FROM users WHERE users.user_id = ? AND users.username = ?

Except, it's easier to write 1 letter rather than a 5 character table name (imagine using the table "integrated_site_user_credentials" everywhere...)

and in your new example

<i>
 </i>SELECT a.author_id, a.author_name, DATE_FORMAT(a.date_posted, '%W, %M %d %l:%i %p'), c.category_id, c.category_name, a.title, a.post_body FROM posts a JOIN categories c ON a.category_id = c.category_id LIMIT 1

@Joseph_WitchardauthorNov 30.2008 — #Made that edit, and it still doesn't work. This my current script/page:

[code=php]<?php
 
 require_once("path_to_connection");
 
 $conn = connection;
 
 $query = "SELECT a.author_id, a.author_name, DATE_FORMAT(a.date_posted, '%W, %M %d %l:%i %p'), a.category_id, c.category_name, a.title, a.post_body FROM posts a JOIN categories c ON a.category_id = c.category_id LIMIT 1";
 
 ?>
 
 
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 <html>
 
 <head>
 
 <title>Rebirth Test Page</title>
 
 <meta name="keywords" content="harry potter,hogwarts,hogwarts rpg,hogwarts online,jk rowling">
 <meta http-equiv="content-type" content="text/html; charset=utf-8">
 <meta http-equiv="cache-control" content="no-cache">
 
 <link href="/css/test.css" rel="stylesheet" type="text/css">
 
 
 
 </head>
 
 <body>
 
 <div id="wrapper">
 
   <div id="headers">
 
   <div id="header1"></div>
 
   <div id="header2">
 
 
 
   </div>
 
   </div>
 
 
 <div id="navigation">
 
   <!-- Begin Navigation Menu -->
 
 
 
   <?php // include the navigation menu
 
   include("../includes/navigation2.html"); ?>
 
   <!-- End Navigation Menu -->
 
 </div>
 
 
 
 
 
 <div id="right">
 
 <p>Hello!</p>
 
 
 
 </div>
 
 <div align="center" id="center">
 
   <div id="welcome">
 
   <p>Welcome to Ultimate Hogwarts: The Rebirth! Ultimate Hogwarts is an online Hogwarts Role Playing Game where fans
   of the Harry Potter books can create their own character, attend Hogwarts, visit the Ministry of Magic, or just 
   interact with other characters in our story! If you would like to know more about this site, please visit the Rules
   page and FAQ page. Once you're ready to enroll, you can go to the Character Applications forum at our message board
   and begin getting your character approved.</p>
 
   <table>
 
   <?php
 
 
 if ($stmt = $conn->prepare($query))
 {
 
 
  if ($stmt->execute()) {
 
  $stmt->bind_result($authorID, $author, $date, $catID, $category, $title, $body);
 
 
 
 
 
   while ($stmt->fetch())
   {
 
   echo "<tr><td><b>" . $title . "</b></td></tr>";
   echo "<tr><td><b>Posted By: " . $author . "on " . $date . "in " . $category . "</b></td></tr>";
   echo "<tr><td>" . $body . "</td></tr>"; }  $stmt->close(); } } else { echo "Statement doesn't execute"; }  $conn->close(); 
 
   /*var_dump($query);*/   ?>
 
   </table>
 
 
 
 
   </div>
 
 </div>
 
 </div>
 
 
 
 </body>
 
 </html>[/code]

The else statement keeps echoing out that $stmt doesn't execute. It's like for some reason, when I write scripts now, even though I write them the same as I always have, $stmt refuses to get set like the older scripts.

Also, I did your alias thing, but logically, shouldn't the way I was doing it before worked too?

@chazzyDec 01.2008 — #take out the back ticks ` first, i think they won't resolve the right way.

and where you have this line: echo "Statement doesn't execute";

Add something like:

[code=php]
 echo "Statement doesn't execute";
 echo "<br/>Possible errors:";
 echo $conn->error;
 echo "<br />";
 echo $stmt->error;
 [/code]

@Joseph_WitchardauthorDec 01.2008 — #Yep, it was the ticks. I thought it was considered good coding practice to use ticks with SQL, so why did that mess it up? Should I remove the ticks from my other SQL scripts?

@chazzyDec 01.2008 — #Yep, it was the ticks. I thought it was considered good coding practice to use ticks with SQL, so why did that mess it up? Should I remove the ticks from my other SQL scripts?[/QUOTE]

Depends. The use of ` is only supported in MySQL. Most other DBMS use a double quote ("word"). You only need to use it when you have a column/table named with a reserved word. When you're using prefixes though, the need for this goes away, and what it's telling the SQL compiler is "Look for something named EXACTLY a.something" when in actuality you only have something.

@Joseph_WitchardauthorDec 01.2008 — #What exactly do you mean by "reserved word"?

So ticks shouldn't be used for queries that use JOIN?

@chazzyDec 02.2008 — #What exactly do you mean by "reserved word"?

So ticks shouldn't be used for queries that use JOIN?[/QUOTE]

REserved words: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

I'm not saying that at all about ticks.

@NogDogDec 02.2008 — #If using the [b]table_name.column_name[/b] format and you also want to quote the identifiers, each part must be quoted separately:

<i>
 </i>SELECT <span><code>table_name</code></span>.<span><code>column_name</code></span>, <span><code>another_table</code></span>.<span><code>another_column</code></span>
 FROM <span><code>table_name</code></span>
 INNER JOIN <span><code>another_table</code></span> ON <span><code>table_name</code></span>.<span><code>user_id</code></span> = <span><code>another_table</code></span>.<span><code>user_id</code></span>

@Joseph_WitchardauthorDec 02.2008 — #All right, thanks everyone?

Also in #PHP _↴

cant get this to work Checking if PHP script / process is running on windows server - Poss Solution Setup Problem:

Success!

Help @Joseph_Witchard spread the word by sharing this article on Twitter...

Tweet This

Database Information Not Outputting

20 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Database Information Not Outputting

20 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

20 Comments(s) _↴

Also in #PHP _↴