[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to use mysql_field_table() in a search query?

Posted on 2011-05-04
12
Medium Priority
?
273 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:FairyBusiness
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 48

Expert Comment

by:hernst42
ID: 35695638
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
 

Author Comment

by:FairyBusiness
ID: 35695655
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
 
LVL 48

Expert Comment

by:hernst42
ID: 35695765
instead of $table = mysql_field_table($result, 10); use $table=$result['srctable'];
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.

 

Author Comment

by:FairyBusiness
ID: 35695821
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
 

Author Comment

by:FairyBusiness
ID: 35695863
how to use UNION ALL with mysql_field_table  ??
0
 
LVL 48

Accepted Solution

by:
hernst42 earned 2000 total points
ID: 35695948
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35696105
Can you output your query and run it directly on the database ?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35698289
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
 

Author Comment

by:FairyBusiness
ID: 35701240
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35701295
That sounds like it.  And do you have a junction table that identifies relationships like which bracelets have rubies?
0
 

Author Comment

by:FairyBusiness
ID: 35706766
No, I have never done a many to many relationship. . .  They confuse me a little
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35706863
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month18 days, 17 hours left to enroll

834 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