PHP/MYSQL data query

['bonehead]

Greetings,

I'd like to figure out some syntax for retrieving the data from a table
when I don't know all the of field names. What I do know are, the name
of the table, the names of the primary key fields, and the names of the
fields I want to use in the "where" clause of the SELECT statement. All
of the examples I've seen in my books only show how to do this by
hard-coding the names of each of the fields, i.e.,

$userid=$query_data["userid"];
$userphone=$query_data["phonenum"];
etc.

What I've tried thus far, is to: first, retrieve the name of a field,
store that in a variable, and then, use that variable in the [""]
argument, but I can't get it to work. I also tried using an index,
unsuccessfully. I am able to get the field names successfully and
display them, so I know that part is working.

At the risk of testing patience but perhaps saving time, here's a code
snippet (trouble line is commented in caps, line 10).

Suggestions/examples appreciated.

//connect to the database
1. $linkid = db_connect();

//retrieve the field names of the specified table:
//the value of $userviewtable was passed from a previous line
2. $result=mysql_list_fields("mydatabasename",$uservi ewtable, $linkid);

//define a generic data query to get data from a selected record
3. $query = "select * from $userviewtable where userid='$userid' and
functional_area='$userviewarea';";

//execute the data query and store the result in a variable
4. $result2 = mysql_query($query);

//fetch the resulting data array from the variable
5. $query_data = mysql_fetch_array($result2);
6. mysql_close($linkid);

//at this point $result holds the field names,
//and $query_data holds the data from the selected record

//loop through the field names and use them as
//the arguments for getting the data values
7. echo "Data for selected record on table $userviewtable<br>";
8. for ($i=0; $i<mysql_num_fields($result); $i++) {
//this line seems to be working
9. $fieldname = mysql_field_name($result, $i);
//THIS IS THE TROUBLE LINE
10. $data_value = $query_data[$fieldname];
//note that I also tried these variations:
//["$fieldname"]; [".$fieldname."]; and [$i];

//result shows field names but no data
11. echo $fieldname: $data_value;
}
//end of snippet

[Reply via newsgroup]

Yeah - and its not as difficult as you might think...

Hint: http://ca.php.net/mysql_fetch_array

Need more?

if you want to get all fields/cells from a record, you don't need to
know their names - instead, SELECT * . For example:-

SELECT * FROM TABLE WHERE condition

(condition being whatever your condition is - for example WHERE ID=35
LIMIT 1)

A brief example:

<?
// Select all data from TABLE myTable where fieldName=whatever
$query="SELECT * FROM myTable WHERE fieldName='whatever'";

// Execute our query
$result=mysql_query($query);

// Write the result of our search into $ourRecords - note the
// MYSQL_ASSOC which returns an associate array (meaning the
// keys of your array will have the field names, and not numbers)
$ourRecords=mysql_fetch_array($result, MYSQL_ASSOC);

?>

The above will make $ourRecords a multi-dimensional array... If you
performed a SELECT * (ie select all fields/cells) and your table has the
following fields, ID,FIRSTNAME,LASTNAME,EMPLOYEE_NUMBER and your MySQL
query returned four records, then you would have

$ourRecords[0][ID]
$ourRecords[0][FIRSTNAME]
$ourRecords[0][LASTNAME]
$ourRecords[0][NUMBER]
$ourRecords[1][ID]
$ourRecords[1][FIRSTNAME]
$ourRecords[1][LASTNAME]
$ourRecords[1][NUMBER]
$ourRecords[2][ID]
$ourRecords[2][FIRSTNAME]
$ourRecords[2][LASTNAME]
$ourRecords[2][NUMBER]
$ourRecords[3][ID]
$ourRecords[3][FIRSTNAME]
$ourRecords[3][LASTNAME]
$ourRecords[3][NUMBER]

I hope the above helps - drop me a byte if any of it is unclear... Note
there are things you can do to make improvements (like
mysql_affected_rows() to count how many records were returned - also,
have MySQL make a sort for you (ORDER BY fieldName) and save PHP the
burden - and reduce the time limit it takes to find your data by putting
LIMIT 5 at the end of your query to limit the result to return maximum
five records...)

randelld

[Reply via newsgroup]

'bonehead wrote:

Greetings,

I'd like to figure out some syntax for retrieving the data from a table
when I don't know all the of field names. What I do know are, the name
of the table, the names of the primary key fields, and the names of the
fields I want to use in the "where" clause of the SELECT statement. All
of the examples I've seen in my books only show how to do this by
hard-coding the names of each of the fields, i.e.,

$userid=$query_data["userid"];
$userphone=$query_data["phonenum"];
etc.

What I've tried thus far, is to: first, retrieve the name of a field,
store that in a variable, and then, use that variable in the [""]
argument, but I can't get it to work. I also tried using an index,
unsuccessfully. I am able to get the field names successfully and
display them, so I know that part is working.

At the risk of testing patience but perhaps saving time, here's a code
snippet (trouble line is commented in caps, line 10).

Suggestions/examples appreciated.

//connect to the database
1. $linkid = db_connect();

//retrieve the field names of the specified table:
//the value of $userviewtable was passed from a previous line
2. $result=mysql_list_fields("mydatabasename",$uservi ewtable, $linkid);

//define a generic data query to get data from a selected record
3. $query = "select * from $userviewtable where userid='$userid' and
functional_area='$userviewarea';";

//execute the data query and store the result in a variable
4. $result2 = mysql_query($query);

//fetch the resulting data array from the variable
5. $query_data = mysql_fetch_array($result2);
6. mysql_close($linkid);

//at this point $result holds the field names,
//and $query_data holds the data from the selected record

//loop through the field names and use them as
//the arguments for getting the data values
7. echo "Data for selected record on table $userviewtable<br>";
8. for ($i=0; $i<mysql_num_fields($result); $i++) {
//this line seems to be working
9. $fieldname = mysql_field_name($result, $i);
//THIS IS THE TROUBLE LINE
10. $data_value = $query_data[$fieldname];
//note that I also tried these variations:
//["$fieldname"]; [".$fieldname."]; and [$i];

//result shows field names but no data
11. echo $fieldname: $data_value;
}
//end of snippet

Sorry, I wrongly had not read the full post...

You're almost there... $query_data should be a multi-dimension array,
true? Why not take the first record, and then take the keys from that
record and it should contain the name of all the fields/cells in a
single row of your database. Thus, untested code follows:

<?
$singleRecord=$query_data[0];
$fieldNames=array_keys($singleRecord);
foreach($fieldNames as $fieldNumber=>$fieldName)
{
print("<br>Cell/Column number $fieldNumber is called $fieldName");
}
?>

I hope that helps - and I hope second time around, that I have
understood your question... sorry for the first post...

randelld

['bonehead]

Reply via newsgroup wrote:

<?
$singleRecord=$query_data[0];
$fieldNames=array_keys($singleRecord);
foreach($fieldNames as $fieldNumber=>$fieldName)
{
print("<br>Cell/Column number $fieldNumber is called $fieldName");
}
?>
Ahhaaahhh! I did not know about the array_keys function...somehow I
*knew* I would find out something I did not know! And guess what, there
it is, right there in the index of my book. Thanks for the tip.

Now the sticky part is still hanging out there:

Assuming that the following lines have executed correctly:

//connect to db, define and execute query, fetch data array:
$linkid = db_connect();
$query = "select * from $userviewtable where userid='$userid';";
$result = mysql_query($query);
$query_data = mysql_fetch_array($result);
mysql_close($linkid);

and, assuming that I *know* that one of the fields is named "userid", I
would retrieve the data from a particular field as follows:

$my_data=$query_data["userid"];

HOWEVER, if I do *not* know the names of the fields, and wanted to
substitute $fieldName from your example above, I presume I would still
retain the square bracket and double quotes, as follows:
$singleRecord=$query_data[0];
$fieldNames=array_keys($singleRecord);
foreach($fieldNames as $fieldNumber=>$fieldName)
{
print("<br>Cell/Column number $fieldNumber is called $fieldName"); //MY NEW LINES HERE
$my_data=$query_data["$fieldName"];
echo $my_data; }

I'll test this in the morning when I'm soburrrr....thanx again....hic