Page 1 of 2

phpmyadmin problem

Posted: Tue Apr 28, 2009 11:50 am
by street_spirit
Hi, I was hoping someone can help I assume this must just be something small. Basically I'm trying to get this: http://www.phpwebcommerce.com/ working on my hosting package, but am having a problem with the databases. The program comes with the database file to import using phpmyadmin but I get the following error each time I try to:

#1044 - Access denied for user 'mellowfe'@'localhost' to database 'plaincart'

I have attached the sql file or you can find it on on the link I provided.

I have created a database through my control panel, assigned it privileges and what not. I called it plaincart as it instructed, but the site insists on adding 'mellowfe_plaincart' would that be it? Not sure I can see anything within the sql file itself when I open it in dreamweaver that I could change to help with that?

Re: phpmyadmin problem

Posted: Tue Apr 28, 2009 1:16 pm
by McInfo
If you want a clean start...

1. Delete the database and user that you already created.

2. Run these queries. Change the strings and identifiers that begin with "plaincart" if necessary.

Code: Select all

CREATE USER 'plaincart_user'@'localhost' IDENTIFIED BY 'plaincart_password';
 
GRANT USAGE ON * . * TO 'plaincart_user'@'localhost' IDENTIFIED BY 'plaincart_password' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
 
CREATE DATABASE IF NOT EXISTS `plaincart` ;
 
GRANT ALL PRIVILEGES ON `plaincart` . * TO 'plaincart_user'@'localhost';
3. In <install directory>/plaincart/library/config.php, change lines 9-13 to

Code: Select all

// database connection config
$dbHost = 'localhost';
$dbUser = 'plaincart_user';
$dbPass = 'plaincart_password';
$dbName = 'plaincart';
4. Run the queries found in <install directory>/plaincart/plaincart.sql

Edit: This post was recovered from search engine cache.

Re: phpmyadmin problem

Posted: Tue Apr 28, 2009 2:15 pm
by McInfo
I noticed a problem with the PlainCart login script. The default password is supposed to be "admin". However, my attempts to log in to the admin page using "admin" as the user name and password failed.

In the tbl_user table, the password "admin" is represented as "43e9a4ab75570f5b", which is 16 characters long. In the login script, the submitted password is compared against the stored password using the SQL function PASSWORD().

I am using MySQL version 5.1.30, and when I run the query:

Code: Select all

SELECT PASSWORD('admin') FROM DUAL;
the result is 43 characters long.
*4ACFE3202A5FF5CF467898FC58AAB1D615029441
To fix this problem, I ran two queries.

1. The first query increases the password field length from 32 to 43 characters.

Code: Select all

ALTER TABLE `tbl_user` CHANGE `user_password` `user_password` VARCHAR( 43 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
2. The second query updates the admin password.

Code: Select all

UPDATE `plaincart`.`tbl_user` SET `user_password` = PASSWORD('admin') WHERE `tbl_user`.`user_name` = 'admin' LIMIT 1;
Then I was able to log in to the PlainCart Admin Control Panel and update the password for the webmaster user.

Edit: This post was recovered from search engine cache.

Re: phpmyadmin problem

Posted: Wed Apr 29, 2009 3:40 am
by street_spirit
Hi Guys, thanks for the help.

I seem to have run into a problem however as I get

SQL query:

CREATE USER 'plaincart_user'@'localhost' IDENTIFIED BY 'plaincart_password';

MySQL said: Documentation
#1227 - Access denied; you need the CREATE USER privilege for this operation

this message, and back in the bluehost control panel it won't let me create a user more then seven charters long and without my domain thing in front of it.

Re: phpmyadmin problem

Posted: Wed Apr 29, 2009 4:30 am
by street_spirit
Well I got this back from my hosting company, looks like my user has to start with mellowfe_whatever

"Hi David. The only way to create a database is through the cPanel tool MySQL Databases. The reason being that mySQL is server-wide, we don't keep separate instances for each person. So in order to ensure that each database and database user are unique, we prepend your cPanel username to all the database names and database usernames. Which means that these will always begin with mellowfe_ and end with the database name and username that you put in. This will need to be reflected in any connection scripts. I'm sorry but that is our setup to keep people from accidenatly overwriting each other's databases. "

Re: phpmyadmin problem

Posted: Wed Apr 29, 2009 9:58 am
by street_spirit
ah ha, looked at it this afternoon, easily solved by editing the sql and using 'mellowfe_plaincart' where it says 'plaincart'.

Thanks mcinfo for the password thing to that would have been the next thing to baffle me!

Re: phpmyadmin problem

Posted: Wed Apr 29, 2009 10:57 am
by street_spirit
Hmm not sure if anyone has this installed themselves but I don't quite understand how the categories is meant to be modified without going into the database itself and doing it manually? It allows you to change the top layer categorises but there's no way of changing the sub categories which are the only thing you can attach to products? I don't really need sub cats at the mo anyway.

Re: phpmyadmin problem

Posted: Wed Apr 29, 2009 12:16 pm
by McInfo
In Admin Control Panel > Category, there is a list of top-level categories (Cars, Manga). You can add a top-level category with the Add Category button. In the list of top-level categories, if you click on the Modify link for a category, you can change its name, description, and image. Alternatively, if you click on the category name, you can see the list of subcategories. For example, if you click on Cars, you will see a list with Mercedes-Benz and Volvo. The subcategories also have Modify and Delete links, and the subcategory page has an Add Category button.

I agree, this design is a little confusing. Especially, it would be nice if the subcategories page had a reference to the appropriate top-level category.

Edit: This post was recovered from search engine cache.

Re: phpmyadmin problem

Posted: Wed Apr 29, 2009 5:48 pm
by street_spirit
ah ha, i see yes thanks.

Re: phpmyadmin problem

Posted: Thu Apr 30, 2009 11:35 am
by street_spirit
...ok last problem i promise!

Trying to get it to upload multiple images per product, not got to displaying it yet but seem to be getting errors when I upload now, I got most of the bugs out but now it seems to be saying there's something wrong with the sql? I get '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 'NOW())' at line 2'. I simply duplicated the pd_image and pd_thumbnail fields and added 2's as you can see.

I have also attached the processing file here:
processProduct.php.zip
(2.08 KiB) Downloaded 397 times

Re: phpmyadmin problem

Posted: Thu Apr 30, 2009 2:48 pm
by McInfo
The immediate problem is that a comma is missing before "NOW" in your query. Also, the field names are out of order.

processProduct.php, lines 50-51
$sql = "INSERT INTO tbl_product (cat_id, pd_name, pd_description, pd_price, pd_qty, pd_image, pd_thumbnail, pd_thumbnail2, pd_image2, pd_date) // Field names out of order
VALUES ('$catId', '$name', '$description', $price, $qty, '$mainImage', '$thumbnail', '$mainImage2', '$thumbnail2', NOW())"; // Comma missing before NOW
If you are going to need multiple images for each product, I recommend that you change your design so that the images and thumbnails are in a separate table.

Edit: This post was recovered from search engine cache.

Re: phpmyadmin problem

Posted: Fri May 01, 2009 4:52 am
by street_spirit
Cool cool thanks, yeah that seems to have done the trick with that error, just trying to work out now how to get it to put both pictures in instead of just creating two of the same picture! Hopefully I'll get there, think it must be to do with the creating of the images process.

Re: phpmyadmin problem

Posted: Tue May 05, 2009 3:25 am
by street_spirit
I can't for the life of me work out how to get this multiple image upload to work for this program, can anyone put me out of my misery at all? Just don't get how it would be possible?

Re: phpmyadmin problem

Posted: Tue May 05, 2009 10:02 pm
by McInfo
Make a new table in your PlainCart database named "tbl_product_image" and move all of the image names from tbl_product into the new table. Drop the pd_image and pd_thumbnail fields from the tbl_product table.

Code: Select all

EXPLAIN `tbl_product_image`;
# +--------------+------------------+------+-----+---------+----------------+
# | Field        | Type             | Null | Key | Default | Extra          |
# +--------------+------------------+------+-----+---------+----------------+
# | pd_img_id    | int(10) unsigned | NO   | PRI |         | auto_increment |
# | pd_id        | int(10) unsigned | NO   |     |         |                |
# | pd_image     | varchar(200)     | YES  |     |         |                |
# | pd_thumbnail | varchar(200)     | YES  |     |         |                |
# +--------------+------------------+------+-----+---------+----------------+
Make a backup of your PlainCart directory, then follow these instructions. These changes will break the admin control panel pages because I didn't take the time to make the necessary changes to the admin scripts. These are only a third of the changes that need to be made. If you study the changes, you should be able to make similar changes to the admin scripts, which will need to be fixed to allow both retrieval and submission of image names from the database.

Code: Select all

--------------------------------------------------------------------------------
+ OPEN                                                                         +
================================================================================
library/product-functions.php
--------------------------------------------------------------------------------
++ FIND
================================================================================
    $sql = "SELECT pd_name, pd_description, pd_price, pd_image, pd_qty
            FROM tbl_product
            WHERE pd_id = $pdId";
--------------------------------------------------------------------------------
+++ REPLACE WITH
================================================================================
    $sql = "SELECT pd_name, pd_description, pd_price, pd_qty
            FROM tbl_product
            WHERE pd_id = $pdId";
--------------------------------------------------------------------------------
++ FIND
================================================================================
    if ($row['pd_image']) {
        $row['pd_image'] = WEB_ROOT . 'images/product/' . $row['pd_image'];
    } else {
        $row['pd_image'] = WEB_ROOT . 'images/no-image-large.png';
    }
--------------------------------------------------------------------------------
+++ REPLACE WITH
================================================================================
    $sql2 = "SELECT pd_image
             FROM tbl_product_image
             WHERE pd_id = $pdId";
   
    $result2 = dbQuery($sql2);
    while ($row2 = dbFetchAssoc($result2)) {
        if ($row2['pd_image']) {
            $row['pd_image'][] = WEB_ROOT . 'images/product/' . $row2['pd_image'];
        } else {
            $row['pd_image'][] = WEB_ROOT . 'images/no-image-large.png';
        }
    }
--------------------------------------------------------------------------------
+ OPEN                                                                         +
================================================================================
library/cart-functions.php
--------------------------------------------------------------------------------
++ FIND
================================================================================
    $sql = "SELECT ct_id, ct.pd_id, ct_qty, pd_name, pd_price, pd_thumbnail, pd.cat_id
            FROM tbl_cart ct, tbl_product pd, tbl_category cat
            WHERE ct_session_id = '$sid' AND ct.pd_id = pd.pd_id AND cat.cat_id = pd.cat_id";
--------------------------------------------------------------------------------
+++ REPLACE WITH
================================================================================
    $sql = "SELECT ct_id, ct.pd_id, ct_qty, pd_name, pd_price, pd.cat_id
            FROM tbl_cart ct, tbl_product pd, tbl_category cat
            WHERE ct_session_id = '$sid' AND ct.pd_id = pd.pd_id AND cat.cat_id = pd.cat_id";
--------------------------------------------------------------------------------
++ FIND
================================================================================
    while ($row = dbFetchAssoc($result)) {
        if ($row['pd_thumbnail']) {
            $row['pd_thumbnail'] = WEB_ROOT . 'images/product/' . $row['pd_thumbnail'];
        } else {
            $row['pd_thumbnail'] = WEB_ROOT . 'images/no-image-small.png';
        }
        $cartContent[] = $row;
    }
--------------------------------------------------------------------------------
+++ REPLACE WITH
================================================================================
    while ($row = dbFetchAssoc($result)) {
        $sql2 = "SELECT pd_thumbnail FROM tbl_product_image WHERE pd_id = {$row['pd_id']} LIMIT 1";
        $result2 = dbQuery($sql2);
        $row2 = dbFetchAssoc($result2);
        if ($row2['pd_thumbnail']) {
            $row2['pd_thumbnail'] = WEB_ROOT . 'images/product/' . $row2['pd_thumbnail'];
        } else {
            $row2['pd_thumbnail'] = WEB_ROOT . 'images/no-image-small.png';
        }
        $cartContent[] = array_merge($row, $row2);
    }
--------------------------------------------------------------------------------
+ OPEN                                                                         +
================================================================================
include/productDetail.php
--------------------------------------------------------------------------------
++ FIND
================================================================================
  <td align="center"><img src="<?php echo $pd_image; ?>" border="0" alt="<?php echo $pd_name; ?>"></td>
--------------------------------------------------------------------------------
+++ REPLACE WITH
================================================================================
  <td align="center"><?php
    foreach ($pd_image as $src) :
        ?><img src="<?php echo $src; ?>" border="0" alt="<?php echo $pd_name; ?>" /><br /><?php
    endforeach;
  ?></td>
--------------------------------------------------------------------------------
+ OPEN                                                                         +
================================================================================
include/productList.php
--------------------------------------------------------------------------------
++ FIND
================================================================================
$sql = "SELECT pd_id, pd_name, pd_price, pd_thumbnail, pd_qty, c.cat_id
        FROM tbl_product pd, tbl_category c
        WHERE pd.cat_id = c.cat_id AND pd.cat_id IN $children
        ORDER BY pd_name";
--------------------------------------------------------------------------------
+++ REPLACE WITH
================================================================================
$sql = "SELECT pd_id, pd_name, pd_price, pd_qty, c.cat_id
        FROM tbl_product pd, tbl_category c
        WHERE pd.cat_id = c.cat_id AND pd.cat_id IN $children
        ORDER BY pd_name";
--------------------------------------------------------------------------------
++ FIND
================================================================================
        extract($row);
--------------------------------------------------------------------------------
+++ REPLACE WITH
================================================================================
        extract($row);
       
        $imgsql = "SELECT pd_thumbnail FROM tbl_product_image WHERE pd_id = {$pd_id} LIMIT 1";
        $imgrslt = dbQuery($imgsql);
        $imgrow = dbFetchAssoc($imgrslt);
        $pd_thumbnail = $imgrow['pd_thumbnail'];
--------------------------------------------------------------------------------
+ DONE                                                                         +
================================================================================
Edit: This post was recovered from search engine cache.

Re: phpmyadmin problem

Posted: Thu May 07, 2009 3:33 am
by street_spirit
Hi McInfo, thanks again I will give that a go hopefully I can work out the rest!