asked on

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

hernst42

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

FairyBusiness

ASKER

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

hernst42

instead of $table = mysql_field_table($result, 10); use $table=$result['srctable'];

FairyBusiness

ASKER

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

FairyBusiness

ASKER

how to use UNION ALL with mysql_field_table ??

ASKER CERTIFIED SOLUTION

hernst42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lukasz Chmielewski

Can you output your query and run it directly on the database ?

Ray Paseur

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.

FairyBusiness

ASKER

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?

Ray Paseur

That sounds like it. And do you have a junction table that identifies relationships like which bracelets have rubies?

FairyBusiness

ASKER

No, I have never done a many to many relationship. . . They confuse me a little

Ray Paseur

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.