Accessing the DB

[Steven300]

Hi everyone. I'm in the middle of writing a contribution.. but have recently hit a brick wall. I'm not totally sure how to grab some data from my own table in the database. I'll show you a section of the code first, then I'll explain a bit further..

    </td>
  </form>
</table>

<?php

global $db;

$subject1 = $db->Execute("SELECT email_templates.subject
                               FROM " . TABLE_EMAIL_TEMPLATES . "
                               WHERE email_templates.email_template_id=1");
    
$entry1 = $db->Execute("SELECT email_templates.entry
                               FROM " . TABLE_EMAIL_TEMPLATES . "
                               WHERE email_templates.email_template_id=1");

echo $subject1;

?>

<script type="text/javascript">

templates.subject1.value = $subject1;
templates.entry1.value = $entry1;

</script>

<!-- body_text_eof //-->

As you can tell, I have a table with a form inside (called templates), it contains a couple of textarea boxes, subject1 and entry1. I'm trying to put $subject1 and $entry1 inside them respectively. The JS bit works fine, but the PHP variables just mentioned don't have the expected data. I put an echo command (shown in red) for $subject1 and it outputs "Object", which isn't right. I'm trying to get the variables into the textareas on page load, which is why none of it is inside functions. I'm not sure whether that could be a problem. The database table exists and looks fine. I also did an echo for TABLE_EMAIL_TEMPLATES and that looks ok.

I realise the amount of code I have displayed above may not be enough to determine the problem. If that's the case, I could always email the contribution's files by PM to anyone who wants to help.

Thanks.

[mischief]

Hi Steven,

you will have to read your values from those variables first. They are just a handle (object). The object holds that data (in an array). There are different way to read the values (and the keys), one is:

while($result = mysql_fetch_assoc($subject1))
foreach($result as $key => $val)
echo '$key: ' . $key . '$val: ' . $val . "<br />\n";


Eg if there are 2 key/data-pairs returned from the db-query,
one from column 'a' and one from column 'b', $result['a'] holds
the value of col 'a' and $result['b'] holds the value retrieved from
column 'b'.

Hope that helps.

Frank

[Steven300]

Hi Frank. Thanks for your reply. Ok I now understand how the object needs to be read first. But I'm not sure how to implement your code. I added it so it's like this..

<?php

    global $db;

    $subject1 = $db->Execute("SELECT email_templates.subject
                               FROM " . TABLE_EMAIL_TEMPLATES . "
                               WHERE email_templates.email_template_id=1");
    
    $entry1 = $db->Execute("SELECT email_templates.entry
                               FROM " . TABLE_EMAIL_TEMPLATES . "
                               WHERE email_templates.email_template_id=1");

while($result = mysql_fetch_assoc($subject1))
foreach($result as $key => $val)
echo '$key: ' . $key . '$val: ' . $val . "<br />\n";

?>

<script type="text/javascript">

templates.subject1.value = $subject1;
templates.entry1.value = $entry1;

</script>

<!-- body_text_eof //-->

Nothing gets displayed for the echo part in the added code. Am I missing something?

[mischief]

Hi,

I'm sorry, seems i cannot help...

I tried it with $best_sellers in the box-include like this:


given in the file:
$best_sellers = $db->Execute($best_sellers_query);


my addition:
while($result = mysql_fetch_assoc($best_sellers))
{
foreach($result as $key => $val)
echo '$key: ' . $key . '$val: ' . $val . "<br />\n";
}


with error_reporting 'on' i could read this:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/zen-cart-v1.3.8a/includes/modules/sideboxes/best_sellers.php on line 67

Fatal error: Cannot use object of type queryFactoryResult as array in /Library/WebServer/Documents/zen-cart-v1.3.8a/includes/modules/sideboxes/best_sellers.php on line 75


I was too fast with my answer, thought this works as any mysql-query but 'queryFactoryResult' seems to make it a different thing. I'm not into Zen Cart and its classes deep enough yet to explain what happens.

Sorry,
Frank

[mischief]

Oh, forgot to mention, the second error message (line 75) was for the following:

i tried:

echo $best_sellers['products_id'];

which didn't work either.

Once again, sorry,

Frank

[mischief]

Found something:
http://www.zen-cart.com/forum/showth...light=db+query

try this:

while(!$subject1->EOF)
{
 echo 'subject: ' . $subject1->fields['subject'];
 $subject1->MoveNext();
}

Maybe this time...

It might happen that echoing something from inside a function etc. gives you a 'header already sent'-php-Error. But $subject1->fields['subject'] should hold the value and you should be able to use it where you need it.

Frank

[Steven300]

Thanks Frank. The code from your last post works great.

[mischief]

glad it works.

By the way, what kind of contribution are you working on?

Frank

[Steven300]

It's basically an admin side tool which is going to have available a number of email templates to choose from. A lot of outbound customer related emails are very similar in content (order delay, review reminder, new sale etc). It will store these templates and make it really easy to add, edit and delete them. A few other features planned as well. I figured some people might struggle a bit with the customer service side of ecommerce.. or equally suitable for busy people who have to keep typing out the same email again and again. It's only my first contribution so I'm not expecting it to set the world alight. But I do think some people could find it useful. What do you think?

[mischief]

Sounds good.
When i was looking for a shop to work with i viewed some of the demo-shops and visited some shops already online built on this or that system. Besides anything else i did not really like the fact that with a lot of shops the standard texts are often equal. Seems you are already a bit more below the surface and found something slightly similar with these e-mail responses to work on.
Good luck.