Database Query's Not Working

[mad_php]

Hi,

 

I have two querys on one of my pages. Ones lists the records and another pulls a record by ID. Both of the query are in include files. See below.

 

news_list.inc.php

<?php



	$sql = "call news_list_proc(:public)";

	$sth = $dbh->prepare($sql);

	if ($dbh->errorCode()<>'00000'){
		die("Error: ".implode(': ',$dbh->errorInfo())."\n");
	}

	$sth->bindParam(':public', $public, PDO::PARAM_INT);

	$sth->execute();

	$news_item_list = array();

	$i=0;

	while ( $rows = $sth->fetch()) 
	{
		array_push ($news_item_list, $rows);

		$date_add = dateconvert($news_item_list[$i]['date_added'],2);
		$news_item_list[$i]['date_added'] = $date_add;

		$date_p = dateconvert($news_item_list[$i]['date_published'],2);
		$news_item_list[$i]['date_published'] = $date_p;

		$date_ex = dateconvert($news_item_list[$i]['date_expired'],2);
		$news_item_list[$i]['date_expired'] = $date_ex;

		$i++;
	}

	$sth->closeCursor();

	$smarty->assign ("news_item_list", $news_item_list);
?>

 

and

 

news_get.inc.php

<?php

	$sql = 'call news_get_proc(:news_id)';
	$sth = $dbh->prepare($sql);

	if ($dbh->errorCode()<>'00000'){
		die("Error: ".implode(': ',$dbh->errorInfo())."\n");
	}

	$sth->bindParam(':news_id', $news_id, PDO::PARAM_INT);

	$sth->execute();

	$news_item = array();

	while ( $rows = $sth->fetch())
	{
		array_push ($news_item, $rows);

		$date_p = dateconvert($news_item[0]['date_published'],2);
		$news_item[0]['date_published'] = $date_p;

		$date_ex = dateconvert($news_item[0]['date_expired'],2);
		$news_item[0]['date_expired'] = $date_ex; 
	}

	$smarty->assign ("news_item", $news_item);

?>

 

They are called from the index file

 

index.php

<?php

$public = 1;
$page = $_GET['p'];
$news_id = 1; 

include_once('Smarty.class.php');					// load Smarty library
include_once('db.class.php');						// DB Connection

$smarty = new Smarty;

$smarty->template_dir = 'dir hidden/templates';
$smarty->config_dir = 'dir hidden/smarty/config';
$smarty->cache_dir = 'dir hidden/smarty/cache';
$smarty->compile_dir = 'dir hidden/smarty/templates_c';

switch ($page)
{
case "news_view" :
	$smarty->assign("news_id", $news_id);
	include('news_get.inc.php');
	include('news_list.inc.php');
	$smarty->display('news_view.tpl');
	break;
default:
	include_once('news_list.inc.php');
	$smarty->display('index.tpl');
	break;
}

?>

 

right now the news_get works. But not the news_list. When i comment out the include('news_get.inc.php'); the news_list works. And vice versa. Can anyone help me? If you need more info let me know.

[mad_php]

I did var_dump on the array and i get array(0) { } for the second include file. And vice versa.

[fenway]

I don't see an sql query anywhere, just gobs of php code.

[mad_php]

$sql = 'call news_get_proc(:news_id)';

[fenway]

That's still php... and how am I supposed to know what that SP does?

[mad_php]

can you take my word for it that the SQL works correctly outside of the php?

[fenway]

can you take my word for it that the SQL works correctly outside of the php?

Sure... if that's the case, then this topic in the wrong forum, and I'll move it.

[mad_php]

this is a php forum. I'm having problems accessing the database. The SQL works, so i was forced to come to a php forum.

[fenway]

this is a php forum. I'm having problems accessing the database. The SQL works, so i was forced to come to a php forum.

 

Yes, but this is a mysql board -- if you say the mysql query works, and I can't see any of the underlying function calls / queries, my hands are tied.  You're using a db class, so it's transparent, if you can't connect, your credentials are incorrect, or your db is not properly set up -- which doesn't seem to be the case if one script works and the other one doesn't.  Since the connection parameters have nothing to do with the query, I tend to suspect the query itself.  I see no place for error messages.

[mad_php]

ok. now we're getting somewhere. hahaha.

 

I'm not getting any obvious errors so i'm suspecting the db connection parameter might be incorrect. Does persisting the connection have anything to do with whats happening to me? Here is my db connection parameters.

 

$dsn = 'mysql:dbname=mydb;host=myhost;
$user = 'username';
$password = 'password';

try {
	$dbh = new PDO($dsn, $user, $password);
}
catch (PDOException $e) {
	die('Connection failed: '.$e->getMessage());
}

[fenway]

Maybe... I'm not familiar with the PDO... I assume there's a close quote on the first line that I don't see.  Have you tried w/o persistent connections?  I assume the length of $rows is zero, and that's how you "know" it's not working?

[mad_php]

yes that closed quote is there. I dont know if the length of the rows are zero. How do i check for that?

[fenway]

Depends on your DB class implementation... I assume you can check that the query executed successfully (hard if it's an SP)?

[mad_php]

how do i check?

 

here is my db.class.php

 

<?php $dsn = 'mysql:dbname=db;host=localhost';
$user = 'user';
$password = 'password';

try {
	$dbh = new PDO($dsn, $user, $password);
}
catch (PDOException $e) {
	die('Connection failed: '.$e->getMessage());
} 

?>

[fenway]

No idea what pdo is.