my problem...categorys/mysql help needed

Dev Articles Community Forums Sponsor:

You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.

September 11th, 2002, 03:19 AM

mrweirdo

Junior Member

Join Date: Sep 2002

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

my problem...categorys/mysql help needed

hello i am having a problem with my reviews script i made. Well the problem is that when i get to the part were you can see the actual review you dont see the name of the category its in instead you get the number of the category. An example can be seen at URL and notice what category says that should be hardware instead it shows a 1. Anyways this number is suposed to link to the reviews category table and then display the category title in its place. But i dont know how to do that. Can someone help?

here is that part of the script:

PHP Code:


		
			
 $dbcnx = dbopen();

    $sql = "SELECT * FROM $reviews_table WHERE id = $review";

    $result = mysql_query($sql) or die("Couldn't Execute Query, MySQL Said: " . mysql_error() ."");

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

    $rposter = $rows["posterid"];

    $rcat = $rows["category"];

    $rtitle = $rows["title"];

    $rcontent = $rows["content"];

    $rrating = $rows["rating"];

    $review .= "

      <tr> 

        <td><b>Review Title: $rtitle posted by $rposter</b></td>

      </tr>

      <tr>

        <td>Category: $rcat</td>

      </tr>

      <tr> 

        <td>Product Rating: $rrating out of 10</td>

      </tr>

      <tr> 

        <td>$rcontent</td>

      </tr>

      <tr> 

        <td>Product Rating: $rrating out of 10</td>

      </tr>

       <tr> 

        <td>&nbsp;</td>

      </tr>";

    mysql_close($dbcnx);

    }

now to show you whats in the database tables:

reviews
id: 1
posterid: mrweirdo
title: Vantec Iceburg Solid Copper VGA/Chipset Cooler Combo
category: 1
content: Recently I had purchased A Vantec Iceburg Solid Co...
comments: 0
rating: 9

reviews_cats
id: 1
category: hardware
description:
image: images/computer.jpg

ok thats it i hope i explained well enought as i tryed to be as clear as posible. Im not all that great at explaining i think. Anyways hope someone can help me out a bit

September 11th, 2002, 08:49 AM

crazytrain81

Contributing User

Join Date: Aug 2002

Posts: 232

Time spent in forums: < 1 sec
Reputation Power: 6

try this

I've experienced a lot of weirdness when acessing returned mysql rows with named indeces. For consistency alone, I prefer to use numbered indeces. For example, to access your category you would use:

PHP Code:


		
			
 $rows[3]

Also, there is no reason to load your $row variables into seperate variables, unless you're having trouble tracking them.

September 11th, 2002, 11:51 AM

natwalker

Junior Member

Join Date: Sep 2002

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Use a JOIN

You need to join the two tables. This will get a little messy because the column names will clash between the two tables. However, I think that the following should work:

SELECT reviews.id, posterid, title, reviews.category, content, comments, rating, reviews_cats.category FROM reviews INNER JOIN reviews_cats ON reviews.category = reviews_cats.id WHERE reviews.id = $review

The column names will then have changed so you'll have
$row["reviews_cats.category"] for the description etc. If some items don't have categories replace the INNER JOIN with a LEFT JOIN to include those rows.

September 12th, 2002, 02:05 AM

mrweirdo

Junior Member

Join Date: Sep 2002

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

ok well i did what you said natwalker and now i get
Couldn't Execute Query, MySQL Said: Not unique table/alias: 'reviews'

any ideas ?

September 12th, 2002, 05:28 AM

natwalker

Junior Member

Join Date: Sep 2002

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

I think that the error is self-explanatory but there could be several reasons for it. The main clue is that in your code you used a variable $reviews_table whereas I only used the constant "reviews".

My guess would be that you have several databases with at least 2 containing tables with the name "reviews". If this is the case you can fix it in two ways. Either fully specify the table names in the query as db_name.reviews and db_name.reviews_cat or you can select the specific database using the mysql_select_db() function.

I'm sorry if this doesn't work but I'm trying to guess what it might be without seeing the rest of the database and knowing what you'd set $reviews_table to. The problem, I think, lies in one of these areas.

... One other thing to bear in mind all references to tables and columns must be unique (so that the database knows exactly which table/column you're talking about). It shouldn't give this error but if a column exists on both tables you must use the prefix table_name.column_name.

Hope this helps.

... Just tried something similar on a db of mine and found that the result column names are not qualified. That is, they only have the column name and not the table name in them. If you select from both tables there will be a clash on the category column. Therefore, either make sure you only have results_cat.category in the result (which will be $rows['category']) or use the numeric indices as suggested by the other poster.

Last edited by natwalker : September 12th, 2002 at 05:50 AM.

September 12th, 2002, 07:45 AM

natwalker

Junior Member

Join Date: Sep 2002

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

After checking URL (try searching on not unique 1066) it looks like the problem may be that the reviews_cat does not have a unique/primary key defined. Either add the index or change the select to do a join simply on the field's value as in:

SELECT ... FROM reviews,reviews_cat WHERE reviews.category = reviews_cat.id

(obviously you'll need to replace ... with the fieldlist).

Note, check the ALTER TABLE stuff in the mySQL helpfile to add an index. I think the following would work

ALTER TABLE reviews_cat ADD PRIMARY KEY(id)

Viewing: Dev Articles Community Forums > Databases > MySQL Development > my problem...categorys/mysql help needed

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!