?
Solved

show all items in database

Posted on 2010-01-12
14
Medium Priority
?
375 Views
Last Modified: 2013-12-12
I have combined two databases to come up with a profile page.

I am having difficulties getting all the items to show. I only shows one. I know i have the LIMIT 1 in the query but that is for the members. How do i get the items to show all?


//PROFILES
$vendorID=mysqli_real_escape_string($myConnection, $_GET['profile']);
$sqlCommand = "SELECT items.*, members.* FROM items INNER JOIN members
ON members.vendorID=items.vendorID WHERE members.vendorID='$vendorID' LIMIT 1";
//---------------------------------------------
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 

    $profiles = '<table border="0" align="center" cellpadding="10">';
	//---------------------------------------------
    while ($row = mysqli_fetch_assoc($query)){ 
    $id               = $row["member_id"];
	$vendorID         = $row["vendorID"];
    $storename        = $row["storename"];
    $storeDescription = $row["storeDescription"];
	$item             = $row["item"];
	$itemNumber       = $row["itemNumber"];
	$category         = $row["category"];
	//--------------------------------------------
	$dir    = "../vendors/".$row["member_id"];
	$files  = scandir($dir);
		foreach($files as $file){
			if( (pathinfo($file, PATHINFO_EXTENSION)=='png') ||	
				(pathinfo($file, PATHINFO_EXTENSION)=='jpg') ||	
				(pathinfo($file, PATHINFO_EXTENSION)=='gif') )
			  {
    $pic = $file;
     break;
  }}
	$ThePicture = $dir.'/'.$pic;
	$user_pic = '<img src="'.$ThePicture.'" width="225px" />';
	//---------------------------------------------
    $profiles .= '
    <tr><td>
    <table width="600" border="1" cellpadding="6">
		  <tr>
			<td rowspan="2" align="center" valign="top">' . $user_pic . '</td>
			<td colspan="2" align="center" valign="middle"><h1><strong>' . $storename . '</strong></h1></td>
		  </tr>
		  <tr>
			<td colspan="2" align="left" valign="top">' . $storeDescription . '</td>
		  </tr>
		  <tr>
			<td>'.$item.'</td>
			<td>'.$category.'</td>
			<td>Add to Cart</td>
		  </tr>
	</table>
    </td></tr>';
} 
$profiles .= '
            </table>  ';
mysqli_free_result($query); 
//*****=====================================================================================*****

Open in new window

0
Comment
Question by:chefkeifer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 26293649
Nope. Limit 1 limits the result set, not some part of the set.

You want to show all the items.

SELECT * FROM items

should be enough.

If you can describe the reason for only accepting 1 member.

Also, as you are setting a WHERE clause to filter to a single entry, the LIMIT 1 is redundant.

Try ...

SELECT items.*, members.* FROM items INNER JOIN members ON members.vendorID=items.vendorID WHERE members.vendorID='$vendorID'

Though, this would only show items for this vendor.

And as the vendorID is common ...

SELECT items.* FROM items WHERE item.vendorID = '$vendorID'

should be what you want.
0
 

Author Comment

by:chefkeifer
ID: 26293809
I used this
SELECT items.*, members.* FROM items INNER JOIN members ON members.vendorID=items.vendorID WHERE members.vendorID='$vendorID'

I have to use this one because there info from the table 'members' that i need..storename and storeDescription.

taking the LIMIT 1 shows all the records but the two selections from the members repeats as well. WHERE i only want the items from that particular vendor to show.

here is a link of what i mean..
http://www.thedepotgibsonmill.com/administrator/profile.php?profile=keifers
the only thing i want to show once is the big picture, storename (Keifers Design), and storeDescription

i hope that makes sense..

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 26294413
Can you show the structure and relationships between items and members and the complete result set.

I think you've got duff data or you are missing a table in the relationship, or the data isn't normalized.

Can you have duplicate items for a single member? That's what it sounds like.


Hmmm..


Does a vendor have more than 1 store? If so, which store do you want to see?



Try adding DISTINCT to the select and drop the LIMIT 1...

SELECT DISTINCT items.* ......



Really, without the structure and data, I'm a little stuck now.

But, please get the info to me and I'm more than happy to help!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chefkeifer
ID: 26295121
as far as the structure data here are a couple of screen shot of how the tables are structured.

the members table really just holds all the vendor info...

and the items holds all the items. one field that is the same in both are vendorID...

i am new to the whole database thing. so if you think i have them structured wrong please give me some ideas on how to arrange better.
items.png
members.png
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26296935
Why not just do two queries?

Select the store name and description and vendorId from members.  Order it any way that makes sense to you.  You might want to use DISTINCT or GROUP BY - since this is a small data set (less than 50,000 rows) you will not have any performance issues.

Iterate over that results set getting the vendorID from each row.  Use the vendorID in a WHERE clause of the second query, which selects from the items table to retrieve the item, item number, category, etc.

Often with these small tables it is faster and easier to achieve logical results with two queries rather than trying to make obne complicated query.

Best regards, ~Ray
0
 

Author Comment

by:chefkeifer
ID: 26297843
Here is what I came up with. Believe or not i did not get a blank white screen like i usually do after writing something myself.

site came up but when clicking on one for the vendors nothing shows up in the frame to the right
http://thedepotgibsonmill.com/administrator/profile.php

I am of course echo it in a table in the body section
<td width="87%">
         <?php echo $profiles; ?>
</td>

I am pretty sure i have written this in a long version...it could be simplified but this all i know to do at this point. getting there though




//PROFILES
$vendorID=mysqli_real_escape_string($myConnection, $_GET['profile']);
$sqlCommand = "SELECT item, itemNumber, category, vendorID FROM items ORDER BY item ASC";
$sqlCmd2 = "SELECT vendorID, storename, storeDescription FROM members WHERE vendorID='$vendorID'"; 
//---------------------------------------------
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error($myConnection)); 
$query2 = mysqli_query($myConnection, $sqlCmd2) or die (mysqli_error($myConnection));
    $profiles = '<table border="0" align="center" cellpadding="5">';
	//---------------------------------------------
    while ($row = mysqli_fetch_assoc($query, $query2)){ 
    $id               = $row["member_id"];
	$vendorID         = $row["vendorID"];
    $storename        = $row["storename"];
    $storeDescription = $row["storeDescription"];
	$item             = $row["item"];
	$itemNumber       = $row["itemNumber"];
	$category         = $row["category"];
	//--------------------------------------------
	$dir    = "../vendors/".$row["member_id"];
	//--------------------------------------------
	$files  = scandir($dir);
		foreach($files as $file){
			if( (pathinfo($file, PATHINFO_EXTENSION)=='png') ||	(pathinfo($file, PATHINFO_EXTENSION)=='jpg') ||	(pathinfo($file, PATHINFO_EXTENSION)=='gif') )
			  {
    $pic = $file;
     break;
  }}
	$ThePicture = $dir.'/'.$pic;
	$user_pic = '<img src="'.$ThePicture.'" width="225px" />';
	//---------------------------------------------
    $profiles .= '
    <tr><td>
		<table width="600" border="0" cellpadding="2" align="center">
  <tr>
    <td align="center">
    <div class="outWrapper">
    	<div id="picture" style="width:230px; height:230px; float:left; vertical-align:middle">'.$user_pic.'</div>
        <div id="storename" style=" width:345px; float:right;">'.$storename.'</div>
        <div id="storeDescription" style=" width:345px; float:right;" align="left"><br />'.$storeDescription.'</div>
    </div><!--end of wrapper -->
    </td>
  </tr>
  <tr>
  	<td>
    <div id="outWrapper">
    	<div id="items" style=" width:450; float:left; vertical-align:bottom;" align="left">
        	'.$item_pic.'&nbsp;<strong>Item:</strong>&nbsp;'.$item.'&nbsp;<strong>Category:</strong>&nbsp;'.$category.'<br />
		</div>
      <div class="form" style="width:25px;">
      		<form action="addToCart">
            <select name="quantity">
            <option value="one">1</option>
            <option value="two">2</option>
            <option value="three">3</option>
            <option value="four">4</option>
            </select>
            </form>
	  </div>  
	  <div class="addToCart" style="width:75px; float:right; vertical-align:bottom;">
        	Add to Cart
        </div>
    </div><!--end of outWrapper -->
    </td>
  </tr>
</table>
    </td></tr>';
} 
$profiles .= '
            </table>  ';
mysqli_free_result($query); 
//*****=====================================================================================*****

Open in new window

0
 

Author Comment

by:chefkeifer
ID: 26308931
Ray did i got about this one the right way...or am i way off?
0
 
LVL 82

Accepted Solution

by:
hielo earned 2000 total points
ID: 26372061
FYI: generally this is a BAD idea:
SELECT *

Or the equivalent:
SELECT tableName.*

Why? Because it select ALL the columns, even the ones you don't need/want.  In your case, your items table has imagecontent field. Based on what you posted, it looks like you are NOT using that field at all in your page. IF that column were populated, you are selecting/extracting those fields whenever you select items.* which would end up consuming resources unnecessarily.

Get into the habit of selecting only the fields that you need.

Having said that, try:

$sqlCommand = "SELECT GROUP_CONCAT(items.item) as item, GROUP_CONCAT(items.itemNumber) as itemNumber, GROUP_CONCAT(items.category) as category, members.member_id, members.vendorID, members.storename, members.storeDescription FROM items INNER JOIN members
ON members.vendorID=items.vendorID WHERE members.vendorID='$vendorID'";

Open in new window

0
 

Author Comment

by:chefkeifer
ID: 26381664
changed the code the above code and nothing shows up in the frame. I do not get any errors either..

http://www.thedepotgibsonmill.com/administrator/profile.php
0
 

Author Comment

by:chefkeifer
ID: 26382591
okey dokey. Hielo it worked...it was a damn semi-colon i accidentally deleted
0
 
LVL 82

Expert Comment

by:hielo
ID: 26385189
>>I do not get any errors either..
By now you should know that whenever this happens, it is either a missing semi-colon or mis-matched brace or mismatched quotation mark, etc.
:)

If you are on a dev machine adjust your php.ini settings to report errors.
0
 

Author Comment

by:chefkeifer
ID: 26385215
what is a dev machine..i am not sure where my php.ini file is either...
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 26389651
Live vs Dev(elopment).

Normally on a development machine you turn on every possible error and warning so you know the code is working.

To find the ini file, create a info.php file ...

<?php phpinfo(); ?>

Save this with your other PHP scripts on the server and load it via the browser.

You'll see a page showing you all the configuration settings and a Configuration File near the top.

That's the php.ini file.

In there, look for error_reporting.

Make it look like ...

error_reporting = -1

You _can_ use the constants E_ALL, but read http://docs.php.net/manual/en/errorfunc.constants.php first to see why this may not be all possible errors/warnings/notices.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 26389696
Whilst you are at this, also change ...

display_errors = On

That will actually show the errors on screen - if you are using PHP to generate images/audio/XML streams/etc, anything which isn't going to be humanly readable on a browser, then I would recommend adding the following lines to your code...

error_reporting(0);
ini_set('display_errors', 0);

Basically, turn off error messages whilst producing non standard output.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question