How can I retrieve a field in mysql table from a variable from a previous query?
I have been trying a structure that looks like this:
Query A)
OOP php to get 1st result set:
$q = “SELECT id, label, parent FROM mytable”;
process associative array in while loop, and
display this to page (call it parent) with:
instantiating the object: $obj = new myclass();
and then: echo $obj->display_myfunction();
B) So where I get lost is adding a second query to get other fields in the same table related to the id from query $q.
Can I write a second query that goes something like:
$y = “SELECT id, parent FROM mytable where this->id = id”;
provided that both queries A & B are in the same class?
I imagine a situation where A) is a menu bar down the left side of a page, and what is active in the menu is displayed in B) on the body of the page.
// grab the incoming variable from the GET string
// typecast it to an integer, will default to 0 (zero) on failure
$the_id = (int) $_GET['id'];
// pass the id to the function
echo $obj->display_parent($the_id);
So far so good. The getAll function with QueryA works fine. So when I make a variable from that query a link to another, or the same, page -which ought to trigger the getSingle function with QueryB- should I structure the query something like:
<a href='DisplayMyClass.php . id = $id'>$parent</a>
or
<a href="$_SERVER['PHP_SELF'] . id = $id">$parent</a>
So far so good, almost. Now that I have the <a href> syntax right I am successfully passing the $id variable to the url in the new page.
But for the life of me I can not seem to use the $id in query to print the results to the content of the new page. Here is what I have been working with. (Same structure as what works elsewhere, i.e. queryA from above.)
public function display_parent($id) {
$query = "SELECT id, parent, label FROM menu WHERE parent = . $this->id";
$result = mysql_query($query);
if ( $result !== false && mysql_num_rows($result) > 0 ) {
while ( $b = mysql_fetch_assoc($result) ) {
$id = stripslashes($b['id']);
$label = stripslashes($b['label']);
echo "<p>the parent for $id is : $label</p>"; // This alone ought to work, right?
$entry_parent .= <<<ENTRY_PARENT
<p>the entry is: $label</p>
ENTRY_PARENT;
}
}
return $entry_parent;
}
The page this query should turn up on is really nothing more than:
include_once('_class/MyClass.php');
$obj = new ClassName();
echo $obj->display_parent();
I am not really grasping the point of your question, what’s the problem with doing this then?
class MyClass {
// variable definitions i.e.
// var $id;
public function getAll (){
$queryA = "select id, parent, label from mytable"; // seems like perfect candidate for alias on parent as Pid?
// process, display, etc.
}
public function getSingle($id) {
$queryB = "select id, parent, label from mytable where parent = " . $id; // or $this->Pid if using alias.
// process, display, etc.
}
} // end class
$a = myClass;
foreach($a->getAll() as $item){
// display ids
}
Oh boy, assumptions.
I think storing a field involves a while loop in query processing, such as:
while ($b = mysql_fetch_assoc($y) ) {
$id = stripslashes($b['id']);
}
If I am completely off at this point stear me right.
Otherwise:
class MyClass {
// variable definitions i.e.
// var $id;
public_function_A {
$queryA = "select id, parent, label from mytable"; // seems like perfect candidate for alias on parent as Pid?
// process, display, etc.
}
public_function_B {
$queryB = "select id, parent, label from mytable where parent = " . $this->id; // or $this->Pid if using alias.
// process, display, etc.
}
} // end class
Or, perhaps in this case each function is better off being in a separate class?
PHP kinks are all worked out; Variables are bouncing around just fine now! On to a mysql question. Is
$query = "SELECT id, parent, label FROM menu WHERE parent =" . $this->id;
PHPs way of saying
WHERE parent LIKE $id? at the mysql command prompt.
When I run QueryB with $this->id I don’t get any results when processing the query. When I write the query with …like $id, however, I do return results when processing the query.
Couple of questions:
Is $this->id a valid parent in your database tables?
Are you running example queries in phpmyadmin (if you use that)?
When you run LIKE, is that the data you want?
PHP kinks are all worked out; Variables are bouncing around just fine now! On to a mysql question. Is
$query = "SELECT id, parent, label FROM menu WHERE parent =" . $this->id;
PHPs way of saying
WHERE parent LIKE $id? at the mysql command prompt.
When I run QueryB with $this->id I don’t get any results when processing the query. When I write the query with …like $id, however, I do return results when processing the query.
Forget PHP for a minute, use a real value and make sure your SQL statement a) is valid and b) returns the exact result you expect from your test data in your database.
For the record, there is no phpmyadmin involved.
‘Like’ and ‘=’ in this query return the same values at the mysql prompt, as the field in question is an integer. If I have any sql questions I’ll post them in that forum. For the time being I need to do some refining, and wrap everything in <div>s.
