How to use mysql_field_table() in a search query?

Hi, I am trying to determine which table a field is coming from out of my search since I am doing a union all for 3 different tables.  I read up on the documentation of this function but I don't get what I'm doing wrong.  I know its not the usual query for this type of function. . . so does anyone know how to make it work with this?

	// search
		$query = "SELECT * FROM bracelets "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT * FROM earrings "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT * FROM necklaces "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $result = mysql_query($query, $conn);	
		confirm_query($result);

	//And we display the results 
	while($results = mysql_fetch_array($result)) { 
	    $table = mysql_field_table($result, 10);

		echo $table . "<p>";
		var_dump($table);
		echo "<div id=\"results\">";
		echo $results['name'] . "<br />"; 
		//echo "<img src=\"" . $results['table'] . "/" . $results['filename'] . ".png\" />";
		echo "</div>";
		//var_dump($results['color']);
	

Open in new window

FairyBusinessAsked:
Who is Participating?
 
hernst42Commented:
Not possible as UNION internal creates a temp table which has no longer the real sourcetable as an accessable information
See http://bugs.php.net/?id=39270
0
 
hernst42Commented:
Rewrite the query to the example , the nyou can access the sourc with $results['srctable']
$query = "SELECT *, 'bracelets' AS srctable FROM bracelets "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT *, 'earrings' AS srctable FROM earrings "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT *, 'necklaces' AS srctable FROM necklaces "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";

Open in new window

0
 
FairyBusinessAuthor Commented:
I tried this but no luck:

	// search
		$query = "SELECT *, 'bracelets' AS srctable FROM bracelets "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT *, 'earrings' AS srctable FROM earrings "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT *, 'necklaces' AS srctable FROM necklaces "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";

        $result = mysql_query($query, $conn);	
		confirm_query($result);
		
	//And we display the results 
		while($results = mysql_fetch_array($result)) { 
	    $table = mysql_field_table($result, 10);

		echo $table . "<p>";
		var_dump($table);
		echo "<div id=\"results\">";
		echo $results['name'] . "<br />"; 
		echo "</div>";
		}

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
hernst42Commented:
instead of $table = mysql_field_table($result, 10); use $table=$result['srctable'];
0
 
FairyBusinessAuthor Commented:
ok, I did this but it returned null

		$query = "SELECT *, 'bracelets' AS srctable FROM bracelets "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT *, 'earrings' AS srctable FROM earrings "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $query .= "UNION ALL SELECT *, 'necklaces' AS srctable FROM necklaces "; 
        $query .= "WHERE MATCH (name, content, color) AGAINST ('$find') ";
        $result = mysql_query($query, $conn);	
		confirm_query($result);
		
		$table = $result['srctable'];
		//And we display the results 
		echo $table . "<p>";
		var_dump($table);

Open in new window

0
 
FairyBusinessAuthor Commented:
how to use UNION ALL with mysql_field_table  ??
0
 
Lukasz ChmielewskiCommented:
Can you output your query and run it directly on the database ?
0
 
Ray PaseurCommented:
Can you please describe in plain language what you're trying to do?  Tell us something like, "I want to find all the bracelets, necklaces and earrings that have rubies in them."

And if that is the sort of thing you want to do, I recommend that you restructure your data base.  Make the class of jewelry (bracelet, necklace, earring) a column and index it.  Make the jewel (ruby) a column and index it.  Then the SELECT WHERE and GROUP BY clauses will be your friends.
0
 
FairyBusinessAuthor Commented:
Well, I have a table called Products, which has bracelets, necklaces, and earrings as categories in it.  And I have a table called materials, which has materials like 'ruby' in it.  Is that what you mean?
0
 
Ray PaseurCommented:
That sounds like it.  And do you have a junction table that identifies relationships like which bracelets have rubies?
0
 
FairyBusinessAuthor Commented:
No, I have never done a many to many relationship. . .  They confuse me a little
0
 
Ray PaseurCommented:
It's not really confusing.  The junction table would have two columns each with an index - the key from Products and the key from Materials.  To establish the relationship that describes a ruby bracelet, you would add a row to the junction table with the Bracelet key and the Ruby key.  Then you can do SELECT statements against the junction table to answer questions like, "What kinds of stones are available for bracelets?" or "What kinds of products have rubies in them?"  Since the junction table will be indexed these queries that tell the relationships will complete instantaneously.  

And that is why we call it a relational data base.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.