[Resolved] [Resolved] mySQL Not Returning Values...?

[bpat1434]

Ok, so I have a database with two tables. Within the tables there are 5 columns.
id, Date, Title, Short_Text, Extnd_Text

What I want is for my php page to connect to the databse, then read the values, and print them in the HTML again.

Problem is, once I connect to the database, nothing happens (or not that I can see).

I've tried to get the page going with numerous remedies including:
arrays, select, request.

I can't get it to work.

You can view the page here:
http://www.winfieldvfd.org/test/read.php?id=1&cat=call_news

Any help would be appreciated.

Thanks.

~Brett

[LarsLai]

hi!
if you want to get help it would be helpful if you post some relevant parts of your code...
your queries seem ok, although you do not need '' with numeric fields (and 'id' surely is numeric).
see you later

[bpat1434]

Sorry about that.

Here ya go:

Show_Call.php

<?php
$id = $_GET['id'];
$cat = $_GET['cat'];

$database = mysql_connect("localhost", "username", "password");
mysql_select_db("db_name", $database);

$tsql = "SELECT 'Title' FROM '".$cat."' WHERE id='".$id."'";
$Title = mysql_query($tsql, $database);
$Ssql = "SELECT 'Short_Text' FROM '".$cat."' WHERE id='".$id."'";
$Short = mysql_query($Ssql, $database);
$Exql = "SELECT 'Extnd_Text' FROM '".$cat."' WHERE id='".$id."'";
$Ext = mysql_query($Esql, $database);
$dsql = "SELECT 'Date' FROM '".$cat."' WHERE id='".$id."'";
$date = mysql_query($dsql, $database);

function newsTitle(){
	global $Title, $database;
	if(!$database){
		echo('WCVFD - Database Connection Error');
	}
	else{
		echo($Title."\n");
	}
}

function bodyText(){
	global $Short, $Ext, $database, $tsql, $Ssql, $Title;
	if(!$database){
		echo('mySQL Database Error!!<br>'.mysql_errno().'<br><br>'.mysql_error().'<br>');
	}
	else{
		$Short = str_replace("\r\n", "<br>", $Short);
		$Ext = str_replace("\r\n", "<br>", $Ext);
		$Ext = stripslashes($Ext);
		$Short = stripslashes($Short);
		echo($Short.'<br>'.$Ext);
		echo("Title Sql: ".$tsql."<br>");
		echo('Short Sql: '.$Ssql);
		echo('<br>Title: '.$Title);
	}
}


function displayDate(){
	global $date, $database;
	if(!$database){
		echo('Database Error');
	}
	else{
		echo($date);
	}
	mysql_close();
}
?>

THat is the only file that deals with the database.

My read.php file calls these functions externally.

So..... can anyone see what I'm doing wrong?

EDIT
If you're wondering about my queries and why I don't just define one variable as the query & answer, it's because I've been trying lots of other things to try and solve this. I googled the internet like 100 times before i posted here.

If you know of a better way to do this, let me know.

And yes, my username and password are correct and my user for the database is allowed full access.

EDIT
Here's the error I get after adding a this code:

$Title = mysql_query($tsql, $database) or die(mysql_error());

You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the right syntax to use near ''call_news' WHERE id='1'' at line 1

PHP version 4.3.8
MySQL version 4.0.20-standard

Thanks for the help.

~Brett

[jonno946]

Why bother querying the database serveral times to fetch items that are all in the same row?

Also, you are not putting the results of your query into an associative array.

try this:

$sql = "SELECT * FROM '" . $cat . "' WHERE id='" . $id . "'";

$result = mysql_query($sql, $database);

$details = mysql_fetch_assoc($result);

then call your database results like $details[Title] etc...

[bpat1434]

Sorry.

n00b to mySQL & PHP integration.

Well, I enjoyed your code very much.

but... I get this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/winfield/public_html/test/show_call.php on line 19

Here's my code:

$query = "SELECT * FROM '".$cat."' WHERE id='".$id."'";
$result = mysql_query($query, $database);
$details = mysql_fetch_assoc($result);

$Title = $details['Title'];
$date = $details['Date'];
$Short = $details['Short_Text'];
$Ext = $details['Extnd_Text'];

And just so I understand you correctly...

mysql_fetch_assoc() will put the data I request into an array with the column titles as the variable correct?

Thanks for the help.

~Brett

[jonno946]

should work, can you show me all of your code?

i take it $cat is the name of the database table?

Jonno

[bpat1434]

well, $cat is retrieved from the URL which is being passed like so:

http://url.com/read.php?id=*&cat=call_news

call_news corresponds to the database table name. ID corresponds to the ID of a certain row. This way users can read certain articles.

and here's my code again:

<?
$id = $_GET['id'];
$cat = $_GET['cat'];

$database = mysql_connect("localhost", "username", "password");
mysql_select_db("database", $database);

$query = "SELECT * FROM '".$cat."' WHERE id='".$id."'";
$result = mysql_query($query, $database);
$details = mysql_fetch_assoc($result);

$Title = $details['Title'];
$date = $details['Date'];
$Short = $details['Short_Text'];
$Ext = $details['Extnd_Text'];

function newsTitle(){
	global $Title, $database;
	if(!$database){
		echo('WCVFD - Database Connection Error');
	}
	else{
		echo($Title."\n");
	}
}

function bodyText(){
	global $Short, $Ext, $database, $tsql, $Ssql, $Title;
	if(!$database){
		echo('mySQL Database Error!!<br>'.mysql_errno().'<br><br>'.mysql_error().'<br>');
	}
	else{
		$Short = str_replace("\r\n", "<br>", $Short);
		$Ext = str_replace("\r\n", "<br>", $Ext);
		$Ext = stripslashes($Ext);
		$Short = stripslashes($Short);
		echo($Short.'<br>'.$Ext);
		echo("Title Sql: ".$tsql."<br>");
		echo('Short Sql: '.$Ssql);
		echo('<br>Title: '.$Title);
	}
}


function displayDate(){
	global $date, $database;
	if(!$database){
		echo('Database Error');
	}
	else{
		echo($date);
	}
	mysql_close();
}
?>

You can view the script in work here. Just click on the Read More link.
http://winfieldvfd.org/test/calls.php

Thanks for the help.

~Brett

[jonno946]

Try this:

<?
$id = $_GET['id']; 
$cat = $_GET['cat']; 

$GLOBALS[$database] = mysql_connect("localhost", "username", "password");

mysql_select_db("database", $GLOBALS[$database]); 

$query = "SELECT * FROM '".$cat."' WHERE id='".$id."'"; 
$result = mysql_query($query, $GLOBALS[$database]); 
$details = mysql_fetch_assoc($result); 

$GLOBALS[$Title] = $details['Title']; 
$GLOBALS[$date] = $details['Date']; 
$GLOBALS[$Short] = $details['Short_Text']; 
$GLOBALS[$Ext] = $details['Extnd_Text']; 

function newsTitle(){ 

if(!$GLOBALS[$database]) {
    echo('WCVFD - Database Connection Error'); 
} 
else{ 
    echo($GLOBALS[$Title]."\n"); 
} 
} 

function bodyText(){ 
    if(!$GLOBALS[$database]){ 
        echo('mySQL Database Error!!<br>'.mysql_errno().'<br><br>'.mysql_error().'<br>'); 
    } 
    else{ 
        $GLOBALS[$Short] = str_replace("\r\n", "<br>", $GLOBALS[$Short]); 
        $GLOBALS[$Ext] = str_replace("\r\n", "<br>", $GLOBALS[$Ext]); 
        $GLOBALS[$Ext] = stripslashes($GLOBALS[$Ext]); 
        $GLOBALS[$Short] = stripslashes($GLOBALS[$Short]); 
        echo($GLOBALS[$Short].'<br>'.$GLOBALS[$Ext]); 
        echo("Title Sql: ".$GLOBALS[$Title]."<br>");
        echo('Short Sql: '.$GLOBALS[$Short_Text]);
        echo('<br>Title: '.$GLOBALS[$Title]); 
    } 
} 


function displayDate(){ 
    if(!$GLOBALS[$database]){
        echo('Database Error'); 
    } 
    else{ 
        echo($GLOBALS[$date]); 
    } 
    mysql_close(); 
} 
?>

Jonno

[bpat1434]

Well, I get this error, as well as the fetch_assoc error:

mySQL Database Error!!
1064

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''call_news' WHERE id='1'' at line 1

That shows up in the bodyText() function.

It was worth a shot.....

Thanks for the help though.

~Brett

[jonno946]

Theres definately something wrong with your query.

Are you sure your TABLE is called call_news and that you have a field called id?

Also try this:

$query = "SELECT * FROM " . $cat . " WHERE id = '" . $id . "'";

Jonno

[bpat1434]

Yes I'm 100% sure that I have a table named call_news.

But removing the '' from the $cat got it working. Any idea as to why that is like that? PM me if you know.

Thanks for the help. Great job.

~Brett

[jonno946]

ahhh great news, glad you got it working!

i should have spotted that before, i always knew mysql was a bit funny with ' ' and table names!

good work 🙂

Jonno

[ahundiak]

Surround table and column names with back ticks (`) not quote marks( '). Two very different characters. Of course, you only really need to use back ticks when reserved words are involved. Like having a column name called date.

[bpat1434]

Well, I'll start off with my php:

<?php
function displayIt(){
	$sql = mysql_connect("localhost", "username", "password");
	mysql_select_db("database");

$query = "SELECT * FROM call_news";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$a = $num_rows-5;
$i = $num_rows;

for($i; $i<=$a; $i--){
	if($a%4==0){
		break;
	}
	else{
		$query = "SELECT * FROM call_news WHERE id='".$i."'";
		$result = mysql_query($query);
		$num_rows = mysql_num_rows($result);
		$display = mysql_fetch_assoc($result);

		$title = $display['Title'];
		$date = $display['Date'];

		$shortTxt = str_replace("\r\n", "<br>", $display['Short_Text']);
		$short = stripslashes($shortTxt);

		echo('<h2>'.$title.'</h2>'."\n");
		echo('<p class="news">'.$short.'<br>'."\n");
		echo('<font style="text-align: left;"><a href="read.php?id='.$i.'&cat=call_news">Read More....</a></font><br>'."\n");
		echo('<p class="date">'.$date.'</p>'."\n");
	}
	$a = $a+1;
}
mysql_close();
}

?>

This is called from:
http://winfieldvfd.org/test/calls.php

Unfortunately, nothing is displayed. I'm doing the same exact thing as in the other script, but it's not working.

Perhaps it's my loop (most likely). In case I'm doing it wrong (which I know I am), I'll explain what I would like to happen:

I want the number of rows to be calculated (which is done). Then I want to loop through the same code displaying the intro text, title, and date of each entry. I want to only show the most recent 5 entries.
So I figured, I'd set $a to 5 less than the number of rows, and go UNTIL my loop got there.

Not displaying the data.... Any idea why not?

Thanks.

~Brett

[ahundiak]

Adding a new question to an existing thread is usually not a good idea. Adding one to a resolved thread is an even worse idea.

However, look at your logic
$num_rows = 7; // For example
$a = $num_rows - 5 = 2;
$i = $num_rows = 7;

for($i; $i <= $a; $i--)

Well gee, 7 is not less than or equal to 2 so the for loop get's skipped.

And I think you are making it too hard. Do something like:

SELECT * FROM call_news ORDER BY whatever DESC LIMIT 5

Where whatever is your date field. This will yield the five most recent records since DESC will reverse sort the records.

[jonno946]

hmm im a bit confused, perhaps this is what you are trying to achieve:

😕

<?php

function displayIt() {

$sql = mysql_connect("localhost", "username", "password"); 
mysql_select_db("database"); 

$query = "SELECT * FROM call_news"; 
$result = mysql_query($query); 

while($display = mysql_fetch_array($result)) {

$title = $display['Title']; 
$date = $display['Date']; 

$shortTxt = str_replace("\r\n", "<br>", $display['Short_Text']);

$short = stripslashes($shortTxt);

echo "<h2>" . $title . "</h2><br>"; 

echo "<p class=\"news\">" . $short . "<br><br>";

echo "<font style=\"text-align: left;\"><a href=\"read.php?id=" . $id . "&cat=call_news\">Read More....</a></font><br><br>";

echo "<p class=\"date\">" . $date . "</p><br>";

}

mysql_close();

} 

?>

[bpat1434]

Thanks for the help guys.

Ok, so I like the SORT BY whatever DESC idea. I think I'm going to use it.

Here's my question:
Once I order it, do I call it normally? or how would I do it?

~Brett

[bpat1434]

WHOA!!

Got it working now....

Thanks for the help.

No more problems to solve.... and if there are..

NEW THREAD!!

~Brett