Choosing row name dynamically

[The_Hat]

I need to dynamically specify the name of the row in my database I want to pull out..

For instance, here is what I am wanting to do..

## $targetTable: this variable changes
## $primary: this variable changes
## $primaryValue: this changes
## $rowName: this changes


$result = mysql_query("SELECT * FROM ".$targetTable." WHERE ".$primary." LIKE '".$primaryValue."'");
while($row = mysql_fetch_array($result)) {
$want = $row['$rowName'];
echo $want ;
}

All the variables get stated higher up in the code but I don't seem able to specify dynamically what the name of the row is I want the data from.. I'm sure it's just syntax, i.e. doubleQuote-period-quote.. something or other.. but I sure can't suss it out..

Any help guys?

[Matthew1980]

Hi there The_Hat

Try:-
(I am assuming at this point that there is a connection already available for use, if not put the reference ie: $conn into mysql_query($result, $conn))

$result = mysql_query("SELECT * FROM `".$targetTable."` WHERE '".$primary."' LIKE '".$primaryValue."'");
while($row = mysql_fetch_array($result)) {
$want = $row[$rowName];
echo $want;

I think that will get you going! Yes concatenating can be a pain can't it! ;-)

Cheers,
MRb

[CyBerAliEn]

My preference for the query... ;)

$result = mysql_query("SELECT * FROM `{$targetTable}` WHERE '{$primary}' LIKE '{$primaryValue}'");

Cleaner looking. :)


Regardless... I don't think the code Matthew posted will do what you want. When you run the query, mySQL will collect the results and return them to PHP. Your options for accessing this data is then to (usually) loop through the "result set", pulling each record out of the result set via an array. Problem is: your row (from mySQL) has no "name". The results of your query are simple a set of records... you'll have a 1st record, a 2nd record, etc. But these records (each record is a row of the result) are not "named".

Perhaps I am misinterpreting what you want? But I don't believe it is possible.

//MATTHEW'S CODE (MINOR CORRECTION OF MISSING BRACE)
$result = mysql_query("SELECT * FROM `".$targetTable."` WHERE '".$primary."' LIKE '".$primaryValue."'");
while($row = mysql_fetch_array($result)) {
$want = $row[$rowName];
echo $want;
}

Matthew's code will run the query. Then loop through the record set. Each record (row) will be returned to PHP as an array in the variable 'row'. The problem with this code is that "row" is an array of the row (a record from the set). This line:
$want = $row[$rowName];
Makes no sense. "rowName" should be the name of a row, right? Let's forget that this is not possible (rows having names) and consider this line. Row IS a ROW. This code is going to find the COLUMN that has the value of 'rowName' and return it as "want". NOW... if you meant 'rowName' as 'columnName'... then this code will work as you wanted. Yay! But if you are truly trying to work with "rowName", then it will not.

Could you further elaborate on what values "rowName" takes on? What is meant by it?

If "rowName" represents something like "4" (meaning you want to return JUST the 4th result), then you have some options. You could modify your query to return just this item, a la:
$rowNum = $rowName; //?

$offset = ($rowNum-1);
$result = mysql_query("SELECT * FROM `{$targetTable}` WHERE '{$primary}' LIKE '{$primaryValue}' LIMIT 1 OFFSET {$offset}");

This will return JUST the record you want. It is BEST to do it this way, because it puts the work on the database, and the database will perform this operation VERY efficiently and quickly!

The other option (following this line of thought) would be to add an increment-er (ie: $i = 0) to your loop that increments (ie: $i++) at the end of each loop iteration. Then you perform a check to see if the current iteration matches your desired "row number" (row name?). But this should be avoided, as you could end up looping through a HUGE record set before getting just the ONE entry you need/want (where as the modified mySQL query above could do it in one-go).

[The_Hat]

O.k. that works great now what if $RowName was specified thusly?

$RowName = $targetList[$i];

Would this be o.k?

Posted without reading: Yes sorry, I meant column name.

[CyBerAliEn]

If "rowName" is actually the name of the column/field... you should change your variable to reflect this! It is otherwise very confusing. It's like naming a variable "fruitArray" if it actually a string; bad practice!

So let's say "rowName" is now "colName".


If the array "targetList" contains a list of column names (fields) for the table... and "i" represents their index (ie: "i=3" and the item with index 3 is 'fullname', which is what you want)... then yes, your code works as:

$colName = $targetList[$i];

[The_Hat]

Sorry, I modified my actual code when I pasted it in the attempt to be less confusing for the forum but ended up making it more so.

################# we didn't find a matching record above so let make the record match the new feed data and make a note of the change //
if (!$foundData){
$result = mysql_query("SELECT * FROM ".$targetTable." WHERE ".$primary." LIKE '".$primaryValue."'");
while($row = mysql_fetch_array($result)) {
$fieldName = $targetList[$i];
$oldValue = $row[$fieldName];
}

This above doesn't work, I can echo out the value for fieldname, but I can't choose the column with that name.

Shortly after this code I update that ROW with new information and then make an entry of the changes into another table so I can track changes in the values over time.

[Matthew1980]

Hi there cyberalien,

//MATTHEW'S CODE (MINOR CORRECTION OF MISSING BRACE)

That's what happens when you copy and paste too quickly - I hadn't dragged the cursor down far enough to encompass the last '}' brace.

WRT: concatenating - either/either, that's just a matter of preference. But the use of back ticks I think stand you in good stead, as they handle other things, so effectively they can be used 'in case' a field name has spaces in it. Good practice IMHO, but doesn't mean to say as it wouldn't function.

Also, what I should have done but didn't was this:-

while($row = mysql_fetch_array($result)) {
echo "<pre>";
print_r($row);
echo "</pre>";
}

That's my approach to see if the data is being retrieved, then once I know that's good-to-go I carry on, there again everybody's methods are different, no two people work exactly the same way, I remember saying that to the ISO guy the other day too :)

Cheers,
MRb

[CyBerAliEn]

Your code is logically sound; it appears now that something/somewhere isn't what it should be (ie: a variable is probably missing).

Modify your code with Matthew's suggestion, such as the following:

if (!$foundData){
$result = mysql_query("SELECT * FROM ".$targetTable." WHERE ".$primary." LIKE '".$primaryValue."'");
while($row = mysql_fetch_array($result)) {
$fieldName = $targetList[$i];
$oldValue = $row[$fieldName];
echo "<hr><pre>";
echo "i($i)\n";
echo "fieldName($fieldName)\n";
echo "oldValue($oldValue)\n";
echo "targetList...\n";
print_r($targetList);
echo "row...\n";
print_r($row);
echo "</pre><hr>";
} 

This should give you all of the values in HTML/readable form so you can SEE what is actually going on. It is possible it is something as simple as "i" being wrong, etc. You need to put debugging code (temporarily) in like this to track and trace where the issue is coming from.

[The_Hat]

The <pre> revealed a space in the name I was passing.. meaning nothing matched the request..

so stoopid.

A trim will hold it for now until I trace where that blank is coming into the system..

Appreciate the help guys..

[CyBerAliEn]

No problem! Even the best often have to perform this type of check to see what is going on.

If the value is coming from "outside" (ie: the user), it could explain why there is a space. A good idea is to generally always "trim()" user input. I have yet to find a reason/situation where I wouldn't want to... and it usually cleans things up nicely.

But certainly, you should trace the value up through your code to see how/why it is being introduced. Especially if it SHOULD NOT be there. (it indicates your code isn't doing what it should or something isn't coded properly, something that should be alarming)

[rocknbil]

LIKE '".$primaryValue."'");

This is the same as = '".$primaryValue."'"); You need to decide which end of the variable you want to wild card, or both.

starts with $primaryValue

LIKE '".$primaryValue."%'");

ends with $primaryValue

LIKE '%".$primaryValue."'");

$primaryValue occurs anywhere in the field

LIKE '%".$primaryValue."'%");