Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

How to ask in sql which table a field is coming from?

Hello, I am writing a function that pulls products from 3 different tables.  It pulls them according to to if they are a featured product or not.

// Displays the featured products randomly 
function featured_products() {
	global $conn;
	$query = "SELECT * FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM necklaces WHERE featured=1 ";
	$result = mysql_query($query, $conn);
	confirm_query($result);

	$output = "";
	// These loops output the subject names with their corresponding pages underneath them
	while ($featured = mysql_fetch_array($result)) {
		shuffle($featured);
		for ($i = 0; $i < 3; $i++) {
		echo "<img src=\"images/" . $pictures[$i] . "\" />";
		$output .= "<a href=\"index.php?product=" . urlencode($images["id"]) . "\">";
		$output .= "<img src=\"images/" . $images["filename"] . "\" />";
	}
	return $output;
}

Open in new window


BUT the problem is when I got to from the  image tag to display the product.  The bracelets, necklaces, and earring images are all in different directories!  So I need to know which table the product came from to set a variable to it to write that in.  So would be something like this:

$output .= "<img src=\"" . $images['table'] . "/" . $images["filename"] . "\" />";

Is that possible?
0
FairyBusiness
Asked:
FairyBusiness
  • 10
  • 7
  • 5
1 Solution
 
hieloCommented:
try:

$query = "SELECT 'bracelets' as theTable, * FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT 'earrings' as theTable, * FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT 'necklaces' as theTable * FROM necklaces WHERE featured=1 ";
0
 
hieloCommented:
the last line on my previous post is missing a comma before "*":
$query .= "UNION ALL SELECT 'necklaces' as theTable, * FROM necklaces WHERE featured=1 ";
0
 
FairyBusinessAuthor Commented:
so then could I say


$output .= "<img src=\"" . $images['theTable'] . "/" . $images["filename"] . "\" />";

??
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
hieloCommented:
>>$output .= "<img src=\"" . $images['table'] . "/" . $images["filename"] . "\" />";

would then be:
$output .= "<img src=\"" . $featured['theTable'] . "/" . $images["filename"] . "\" />";
0
 
Michael701Commented:
try this, i think the syntax is correct

then you can refer to it as this within the loop

$featured['from_table']


SELECT *, 'bracelets' as from_table FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'earrings' as from_table FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'necklaces' as from_table FROM necklaces WHERE featured=1 ";

Open in new window

0
 
hieloCommented:
>>so then could I say
No. You have:

while ($featured = mysql_fetch_array($result))

so the  result is in $featured

Also, I suggest you change mysql_fetch_array() to mysql_fetch_assoc() since you don't need the numeric indices.
0
 
Michael701Commented:
Wow, all i did was sneeze and I became the 5th reply.

Isn't this place great?
0
 
FairyBusinessAuthor Commented:
@michael lol

ok well I did this:

// Displays the featured products randomly 
function featured_products() {
	global $conn;
	$query = "SELECT *, 'bracelets' as from_table FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'earrings' as from_table FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'necklaces' as from_table FROM necklaces WHERE featured=1 ";
	$result = mysql_query($query, $conn);
	confirm_query($result);

	$output = "";
	// These loops output the subject names with their corresponding pages underneath them
	while ($featured = mysql_fetch_assoc($result)) {
		shuffle($featured);
		for ($i = 0; $i < 3; $i++) {
		echo "<img src=\"images/" . $featured[$i] . "\" />";
		$output .= "<a href=\"" . $featured['from_table'] . ".php?item=" . urlencode($featured["id"]) . "\">";
		$output .= "<img src=\"" . $featured['from_table'] . "/" . $featured["filename"] . "\" />";
		}
	}
	return $output;
}

Open in new window


but I am getting these error messages:

http://auroriella.com/index.php
0
 
FairyBusinessAuthor Commented:
You can see at the bottom of the page where Its trying to load images
0
 
hieloCommented:
why shuffle()?
If you are trying to randomize the results, first get all the results, THEN do shuffle().
0
 
Michael701Commented:
Did the sql work before the as from_table was added?

I think you're missing a } to go with the for loop

And your html <a isn't matched with a </a>
0
 
FairyBusinessAuthor Commented:
I was just gonna say it should be this:

function featured_products() {
	global $conn;
	$query = "SELECT *, 'bracelets' as from_table FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'earrings' as from_table FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'necklaces' as from_table FROM necklaces WHERE featured=1 ";
	$result = mysql_query($query, $conn);
	confirm_query($result);

	$output = "";
	// These loops output the subject names with their corresponding pages underneath them
	while ($featured = mysql_fetch_assoc($result)) {
		//shuffle($featured);
		//for ($i = 0; $i < 3; $i++) {
		$output .= "<a href=\"" . substr($featured['from_table'], 0, -1) . ".php?item=" . urlencode($featured["filename"]) . "\">";
		$output .= "<img src=\"" . $featured['from_table'] . "/" . $featured["filename"] . ".png\" /></a>";
		//}
	}
	return $output;
}

Open in new window


ok when I don't try to shuffle it this happens it works!  I was wanting to shuffle them though so that it wouldn't always show the same thing on the home page.  Is that possible?
0
 
hieloCommented:
try:
0
 
hieloCommented:
sorry - the code did not attach earlier.
// Displays the featured products randomly 
function featured_products() {
	global $conn;
	/*
	$query = "SELECT * FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM necklaces WHERE featured=1 ";
	*/
	$query = "            SELECT 'bracelets' as theTable,  id, filename FROM bracelets WHERE featured=1 ";
    $query .= " UNION ALL SELECT 'earrings'  as theTable,  id, filename FROM earrings WHERE featured=1 ";
    $query .= " UNION ALL SELECT 'necklaces' as theTable,  id, filename FROM necklaces WHERE featured=1 ";
	$result = mysql_query($query, $conn) ;
	confirm_query($result);

	$output = "";
	// These loops output the subject names with their corresponding pages underneath them
	$featured=array();
	while ($featured[] = mysql_fetch_assoc($result))
	{
		//do nothing here
	};

	//"randomize"
	shuffle($featured);

	foreach($featured as $images){
		echo "<img src=\"images/" . $images['filename'] . "\" />";
		$output .= "<a href=\"index.php?product=" . urlencode($images['id']) . "\">";
		$output .= "<img src=\"images/" . $images['filename'] . "\" />";
	}
	return $output;
}

Open in new window

0
 
FairyBusinessAuthor Commented:
I tried but didnt work in the image and link tags I need to know which table to direct to:

$output .= "<a href=\"" . substr($images['from_table'], 0, -1) . ".php?item=" . urlencode($images["filename"]) . "\">";

$output .= "<img src=\"" . $images['from_table'] . "/" . $images["filename"] . ".png\" /></a>";

Something like that. Images is not the directly for them nor does it go anyone in the link.  It was just an example.

this works but do you know how to randomize his?

// Displays the featured products randomly 
function featured_products() {
	global $conn;
	$query = "SELECT *, 'bracelets' as from_table FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'earrings' as from_table FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT *, 'necklaces' as from_table FROM necklaces WHERE featured=1 ";
	$result = mysql_query($query, $conn);
	confirm_query($result);

	$output = "";
	// These loops output the subject names with their corresponding pages underneath them
	while ($featured = mysql_fetch_assoc($result)) {
		//shuffle($featured);
		//for ($i = 0; $i < 3; $i++) {
		$output .= "<a href=\"" . substr($featured['from_table'], 0, -1) . ".php?item=" . urlencode($featured["filename"]) . "\">";
		$output .= "<img src=\"" . $featured['from_table'] . "/" . $featured["filename"] . ".png\" /></a>";
		//}
	}
	return $output;
}

Open in new window

0
 
hieloCommented:
copy and paste the following:
// Displays the featured products randomly 
function featured_products() {
	global $conn;
	/*
	$query = "SELECT * FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM necklaces WHERE featured=1 ";
	*/
	$query = "            SELECT 'bracelets' as theTable,  id, filename FROM bracelets WHERE featured=1 ";
    $query .= " UNION ALL SELECT 'earrings'  as theTable,  id, filename FROM earrings WHERE featured=1 ";
    $query .= " UNION ALL SELECT 'necklaces' as theTable,  id, filename FROM necklaces WHERE featured=1 ";
	$result = mysql_query($query, $conn) ;
	confirm_query($result);

	$output = "";
	// These loops output the subject names with their corresponding pages underneath them
	$featured=array();
	while ($featured[] = mysql_fetch_assoc($result))
	{
		//do nothing here
	};

	//"randomize"
	shuffle($featured);

	foreach($featured as $images){
		echo "<img src=\"images/" . $images['filename'] . "\" />";
		$output .= "<a href=\"index.php?product=" . urlencode($images['id']) . "\">";
		$output .= "<img src=\"images/" . $images['theTable'] .'/'.$images['filename'] . "\" />";
	}
	return $output;
}

Open in new window

0
 
Michael701Commented:
Ok, Heilo: Do you have an idea as to why from_table isn't part of the results?
0
 
FairyBusinessAuthor Commented:
No, this can't work because of this:

$output .= "<a href=\"index.php?product=" . urlencode($images['id']) . "\">";
$output .= "<img src=\"images/" . $images['theTable'] .'/'.$images['filename'] . "\" />";

I am not using a directory called images for this.  There are three directories: bracelets, necklaces, earrings. And the url must be like this

http://auroriella.com/bracelet.php?item=b9

so must be something like

$output .= "<a href=\"" . substr($featured['from_table'], 0, -1) . ".php?item=" . urlencode($featured["filename"]) . "\">";

but they can go to different  categories. I use the substr to make them not plural


0
 
hieloCommented:
>>Ok, Heilo: Do you have an idea as to why from_table isn't part of the results?
My original query does NOT have 'from_table'.


>>I use the substr to make them not plural
Edit the query directly:
// Displays the featured products randomly
function featured_products() {
      global $conn;
      /*
      $query = "SELECT * FROM bracelets WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM earrings WHERE featured=1 ";
    $query .= "UNION ALL SELECT * FROM necklaces WHERE featured=1 ";
      */
      $query = "            SELECT 'bracelet' as theTable,  id, filename FROM bracelets WHERE featured=1 ";
    $query .= " UNION ALL SELECT 'earring'  as theTable,  id, filename FROM earrings WHERE featured=1 ";
    $query .= " UNION ALL SELECT 'necklace' as theTable,  id, filename FROM necklaces WHERE featured=1 ";
      $result = mysql_query($query, $conn) ;
      confirm_query($result);

      $output = "";
      // These loops output the subject names with their corresponding pages underneath them
      $featured=array();
      while ($featured[] = mysql_fetch_assoc($result))
      {
            //do nothing here
      };

      //"randomize"
      shuffle($featured);

      foreach($featured as $images){
            echo "<img src=\"images/" . $images['filename'] . "\" />";
            $output .= "<a href=\"" . $images['theTable'] . ".php?item=" . urlencode($images['id']) . "\">";
            $output .= "<img src=\"images/" . $images['theTable'] .'/'.$images['filename'] . "\" />";
      }
      return $output;
}
0
 
Michael701Commented:
Hielo: I know. *I* had from_table, you had theTable. I just didn't see why my sql command wasn't returning from_table in the results.

Looks like whatever he did it's not returning the missing index errors anymore.
0
 
FairyBusinessAuthor Commented:
Thanks!
0
 
hieloCommented:
>>I just didn't see why my sql command wasn't returning from_table in the results.
The issue is that FairyBusiness was calling shuffle() within the while().  It shuffles the VALUES of the given array, so it "destroy" pre-existing KEYS and/or INDICES.

>>Looks like whatever he did
Considering the handle has Fairy in the name, I would guess it's a "she" :)

>>it's not returning the missing index errors anymore.
you need to first "collect" all the results onto an array, then shuffle that array. Each element in that array is an associative array from each query row.
0

Featured Post

Independent Software Vendors: 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!

  • 10
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now