Variable within a variable

Get text with PHP variables from data base

[ParasiteHarry]

I am loading the divs of my webpages into a MySQL datbase and then extracting them for display. I am struggling to get the variables loaded into the database to be interpreted to text.

eg the database contains the footer division:

<div id="footer">
<p>&copy; $date <a href="index.php">Bovine Trypanosomiasis Consortium</a> </p>
</div>


I want the $date to display on the webpage but I get the literal '$date' or nothing when using code like this:
<?php
//Get the footer
$sql = "select divText from standardContent where division = 'footer'";
$rs = mysql_query($sql,$conn);
$footer = mysql_fetch_array($rs, MYSQL_ASSOC);

//get the main page text with its date
$sql = ("select pageText, updateDate from pages where id = $id");
$rs = mysql_query($sql,$conn);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
$date = $row['updateDate'];

//print them out
echo ($row['pageText']);
echo ($footer['divText']);
?>

If I put the footer text in a file and use an include the date comes out fine.

I have tried various combinations of inverted commas and concatenations arround $date in the footer text. I have also tried using a variable variable eg: echo ($$footer['divText']);
The statement is within <?php ?> delimiters so adding more seems superfluous; I still tried but it made no difference.

None of which do the trick. Any suggestions would be most gratefully received.

Harry

[Matthew1980]

Hi there parasiteHarry,

Welcome aboard :)[webmasterworld.com ]

>>If I put the footer text in a file and use an include the date comes out fine.

That's exactly as it should be, using the msql_fetch_array() needs to be run through a while loop to run through each row that is stored in the DB, though if you only have a single row you can quite easily use mysql_fetch_object to access that instead, but generally for multiple rows, a while loop using either mysql_fetch_array or mysql_fetch_object will do the job.

This wouldn't work either because you need to quote the values:-

$sql = "select pageText, updateDate from pages where id ='$id'";

I have formatted it better in the code below, but I thought I should point that out ;)

Try this anyway:-

<?php
//Get the footer
$sql = "SELECT `divText` FROM `standardContent` WHERE `division` = 'footer'";
$rs = mysql_query($sql,$conn) or die(mysql_error());//use the error handler while developing :)

//loop through record set returned from query
while($footer = mysql_fetch_array($rs, MYSQL_ASSOC)){
echo $footer['column_name'];//this will dsiplay each iteration of date stored in the column
}

//get the main page text with its date
$sql = "SELECT `pageText`, `updateDate` FROM `pages` WHERE `id` = '".$id."' ";//formatted this better
$rs = mysql_query($sql,$conn) or die(mysql_error());//error handler only for development

//loop through record set returned from query
while($row = mysql_fetch_array($rs, MYSQL_ASSOC)){
//print them out
echo $row['updateDate'];//this will print each row in the updateDate column until the end of the record set
echo $row['pageText']);
echo $footer['divText'];
}
?>

You get the idea, have a play with that, but you see the logic there. Just ensure as you have quoted the column names correctly, that can catch you out from time to time ;)

Hope this helps you a little,

Cheers,
MRb

[ParasiteHarry]

Hi Mathew Thanks for your quick reply but unfortunately my system does not seem to like all those inverted commas. (I am using JEdit on Mac 10.5.8 for scripting).

I included a simplified version of my code in the question a full snippet including the extra inverted commas is:

$sql = "select 'division', 'divText' from 'standardContent'";
$rs = mysql_query($sql,$conn);
$divs = array();
while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
$divs[$row['division']] = $row['divText'];

}

echo ($divs['footer']);

This now generates an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 9



I had been thinking that the problem was not with the sql because I can succesfully put a value into $date that is displayed when it is in an include but not when it is in a variable.

BTW thanks for pointing me at mysql_fetch_object I had been looking for the php equivalent of perl selectrow_array

Cheers

Harry

[ParasiteHarry]

Hi Mathew Thanks for your quick reply but unfortunately my system does not seem to like all those inverted commas. (I am using JEdit on Mac 10.5.8 for scripting and development on localhost).

I included a simplified version of my code in the question a full snippet including the extra inverted commas is:

$sql = "select 'division', 'divText' from 'standardContent'";
$rs = mysql_query($sql,$conn);
$divs = array();
while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
$divs[$row['division']] = $row['divText'];

}

echo ($divs['footer']);

This now generates an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 9



I had been thinking that the problem was not with the sql because I can succesfully put a value into $date that is displayed when it is in an include but not when it is in a variable.

BTW thanks for pointing me at mysql_fetch_object I had been looking for the php equivalent of perl selectrow_array

Cheers

Harry

[ParasiteHarry]

Hi Mathew
Just realised that you are using backward sloping ticks not inverted commas. I have tried them now. They do not cause fatal errors but do not get the $date interpolated either.

Thanks

Harry

[Matthew1980]

Hi there parasiteharry,

Aye, backticks for tablenames, column names & single quotes for values to reference column names. Backticks make it easier to accommodate column names with spaces in etc, and IMHO makes the code easier to read & digest.

Ok, what do you have now for code? Are you just trying to echo the date that is in the DB, if so, you seem to be going the long way around for this, you could just echo the date(); function and set the relevant format using something like date("Y M D"); to echo out todays date, or have I missed the point there?

Always good to see alternative ways of doing the same thing, makes the day more interesting ;)

Cheers,
MRb

[rocknbil]

A few corrections, sorry . . .

This wouldn't work either because you need to quote the values:-
$sql = "select pageText, updateDate from pages where id ='$id'";

You do not need to quote numeric data types. In fact, this is a common cause of empty results. If $id is empty, the query will still run but it won't return any rows. Un-quote it, and the query will error if $id is empty - which is a good thing in some cases, it alerts you to the problem. (Which I'm guessing is not the case, as it didn't kick an error in your original?)

Just ensure as you have quoted the column names correctly,

Raising this point because it diverts attention from the problem, this is not it. While this is good practice, it is not required. It is only required if you have column names that confict with reserved words such as data types or functions. Example, date is a data type, now() is a function, so you would need to backtick field names in this query:

select `date`, `now` from `table`;

eg the database contains the footer division: ....
I want the $date to display on the webpage

This is one record you need correct? There should be one matching record for this query, is that right? Then you do not need a while loop. A single query, or more safely, a query wrapped in an IF is all that's required. Both below.

Now to your problem: is this the entire code? Have you echo'ed the value of $id? I don't see where $id is getting a value (but as mentioned, let's assume it's OK, it didn't error in your original query.)

Add an "or" to your queries, and echo out a message. What this does for you is lets you know if a) the query failed, or b) there were no rows, you need to know both, this is error trapping. I've eliminated some superfluous stuff, what does this do?

<?php 
// Set these to null, just in case 
$footer=$date=$pageText=null; 
// 
//Get the footer 
$sql = "select divText from standardContent where division = 'footer'"; 
$rs = mysql_query($sql,$conn) or die("could not query for footer"); 
if ($row = mysql_fetch_array($rs)) {  
 $footer = $row['divText']; 
} 
else { echo "Could not get footer"; } 
// 
//get the main page text with its date 
$sql = "select pageText, updateDate from pages where id = $id"; 
$rs = mysql_query($sql,$conn) or die("could not get page text and date"); 
if ($row = mysql_fetch_array($rs)) { 
 $date = $row['updateDate']; 
 $pageText = $row['pageText']; 
} 
else { echo "could not get date and page text"; } 
//print them out 
echo "$pageText $date $footer"; 
?> 

[ParasiteHarry]

Thanks Rocknbil
$id is an integer;

Your script outputs the:
page text;
the date (2010-07-02); //so the database query is working fine
the footer string containing $date not 2010-07-02;

If I set
$footer = "some text $date some text";
The script outputs:
"some text 2010-07-02 some text";

My current supposition is that the string emerges from the database as a single quoted string which is why variables cannot be translated. I have tried various combinations of single and double quotes when putting it into the database without success.
I have been looking for a way of converting single quoted strings to double quoted strings without any luck so far.

Thanks again
Harry

[ParasiteHarry]

Thanks Rocknbil
$id is an integer;

Your script outputs the:
page text;
the date (2010-07-02); //so the database query is working fine
the footer string containing $date not 2010-07-02;

If I set
$footer = "some text $date some text";
The script outputs:
"some text 2010-07-02 some text";

My current supposition is that the string emerges from the database as a single quoted string which is why variables cannot be translated. I have tried various combinations of single and double quotes when putting it into the database without success.
I have been looking for a way of converting single quoted strings to double quoted strings without any luck so far.

Thanks again
Harry

[rocknbil]

<facepalm> So THIS is what's stored in footer text.

<div id="footer">
<p>&copy; $date <a href="index.php">Bovine Trypanosomiasis Consortium</a> </p>
</div>

Sorry, I was distracted by this.

$date = $row['updateDate'];

Right, that will always be a literal set of characters and you're not doing anything for the substitution. You can make this work, but I suggest a substitution marker in your stored footer. something like this:

<div id="footer">
<p>&copy; |DATE| <a href="index.php">Bovine Trypanosomiasis Consortium</a> </p>
</div>

Then sub out the marker for the variable $date after reading it into memory. Both solutions below.

......
else { echo "could not get date and page text"; }
//
// Now replace '$date' literal in footer with $date
$footer = str_replace('$date',$date,$footer);
//print them out
echo "$pageText $footer";
?>

Or, if you use a marker,

......
else { echo "could not get date and page text"; }
//
// Now replace '|DATE|' with $date
$footer = str_replace('|DATE|',$date,$footer);
//print them out
echo "$pageText $footer";
?>

The first solution will work only because of the single quotes used on '$date' in str_replace, $date stays literal. Variables only interpolate in double quotes or "print to here" blocks.

An aside, have a look at the date_format [dev.mysql.com] functions in the mySQL manual, if you want 07/02/2010 instead of 2010-07-02,

$sql = "select pageText, date_format("%m/%d/%Y",updateDate) from pages where id = $id";

Or July 2nd, 2010

$sql = "select pageText, date_format("%M %D, %Y",updateDate) from pages where id = $id";

[ParasiteHarry]

Thanks RocknBil that worked a treat. It would have been weeks if ever before I thought of swapping out the literal.

Thanks also for the tip on the date format that I had also been wanting to improve.
Turns out the parameters are in reverse order and I had to reassign the name to the updateDate so that the hash could see it:
$sql = ("select pageText, date_format(updateDate,'%D %M %Y') as updateDate from pages where id = $id ");

But now it looks much prettier.

Thanks again

Harry

[rocknbil]

Man, I'm batting 1000 today, right . . . value first, format second, field->format doh . . . . well good it's sorted. Though it does no harm (at least, don't think it does) you don't need the () around your assignment to $sql.