Unexpected Results of MySQL Insert & Foreach

[darrin365]

This script:
[code]<?php
global $database, $my;

if ($my->gid > 0) {

$userid = ($my->id);

echo $userid;

$query = "SELECT trip_id FROM dtd_trip2user"
."\n WHERE user_id = '$userid'"
;
$database->setQuery( $query );
$rows = $database->loadObjectList();

foreach( $rows = $row ) {

$query = "SELECT trip_name FROM dtd_trips"
."\n WHERE trip_id = '$row'"
;
$database->setQuery( $query );
$trip_name = $database->query();

echo $trip_name;
}

} else {
echo "Registered users can create their own custom trip guides. Start yours today!";
}
?>
[/code]
is returning ths error:
Parse error: parse error, unexpected ')' in xxx/yyy//components/com_tripmaker/tripmaker.html.php on line 60

I can't figure out why.

I'm trying to pull the trip_ids from an intersect table and then the corresponding trip names from my trips table on my Joomla site.

Any ideas?

[RobinTibbs]

the foreach syntax is wrong i think

try foreach ($rows as $row)

hope that helps :)

[darrin365]

Yup. Just spotted that. Now my result is:
Resource id #31Resource id #32Resource id #33Resource id #34Resource id #35

???

[Crimpage]

I don't know what database class you are using, but it looks like $database->loadObjectList(); doesnt actually return the results, only a resource id / pointer to the results.

You still need to do a fetchrows function to pull the actual results from the resource pointer.

I use the mysql class from phpbb so I do:

[code]$sql = "whatever";
$result = $database->sql_query($sql); <- that runs the query and returns the resourceid.\
while ($row = $database->sql_fetchrow($result))
{

do stuff;

}[/code]

All of that actually itterates through the resourceid link and pulls the actual results.

- Dave.

[darrin365]

Thanks, Dave. I'll check it out. I know it's not a built-in class. Is it a standard PHP thing?

[Jessica]

I think that's ADODB.
It's not standard, as it's not built-in...

What do you call to create $database?

[darrin365]

Database.php is one of Joomla's include files. It has several functions built in that let those of us who are PHP stupid take shortcuts, such as $database->loadObjectList which is:
[code]function loadObjectList( $key='' ) {
if (!($cur = $this->query())) {
return null;
}
$array = array();
while ($row = mysql_fetch_object( $cur )) {
if ($key) {
$array[$row->$key] = $row;
} else {
$array[] = $row;
}
}
mysql_free_result( $cur );
return $array;
}[/code]

I think Dave is right, it's either not the right function to use or I'm using it incorrectly. I've tried to study other components and figure out what to do, but I'm not doing so hot.  :D

[Braclayrab]

You are doing many unnecessary DB reads.  I suspect you'd be better off with a join(it should be much faster):

SELECT trip_id, trip_name FROM dtd_trip2user INNER JOIN dtd_trips on dtd_trips.trip_id = dtd_trip2user.trip_id

[darrin365]

I had a feeling that might have been the case, but I haven't learned to use JOINs yet. Correct me if I'm wrong, but a result of multiple rows of the same column would not be considered an array, correct? It would be an array if it included multiple columns of info?

[Braclayrab]

tbh, I don't know.  I've mostly worked with ASP/MSSQL, ASP gives you an 'asp recordset' object which is identical no matter how many columns/rows you have.  You could just leave out the trip_id if you don't actually need it.

[Crimpage]

It will still put it in an array. The base SQL function is mysql_fetch_array($resource_id);

Look for that function within Joomla's DB class and you should be able to find the right function.

Dave

[darrin365]

No mysql_fetch_arrays. There are mysql_fetch_row and mysql_fetch_assoc in there. I finally settled on:
[code]<?php
global $database, $my;

if ($my->gid > 0) {

$userid = ($my->id);

echo $userid;

$query = "SELECT trip_id, trip_name FROM dtd_trip2user"
."\n FROM dtd_trip2user"
."\n INNER JOIN dtd_trips"
."\n on dtd_trips.trip_id = dtd_trip2user.trip_id"
;
$database->setQuery( $query );
$rows = $database->loadResultArray();
print_r( $rows );
} else {
echo "Registered users can create their own custom trip guides. Start yours today!";
}
?>[/code]
But it still doesn't work. It returns no results, just blankness. I'm too tired to sort it out tonight. I'll try again tomorrow. It does seem like there should be a foreach in there since I'm returning multiple rows of data (just one column). But that gets me a:
Warning: Invalid argument supplied for foreach()
error message.

Thanks for your help and any further ideas you may have.

[Braclayrab]

I had to look into this(I'm gonna be starting some database work tomorrow anyway)
The built-in mysql_select_db function will allow you to query multiple rows/columns all at once.

<?php
$connection = mysql_connect($_ENV['DATABASE_SERVER'],$username, $password);
mysql_select_db("Main", $connection) or die("Unable to select database");
$query="SELECT * FROM _Users";
$result=mysql_query($query) or die('Query failed: ' . mysql_error());
$numrows=mysql_numrows($result);
for ( $counter = 0; $counter < $numrows; $counter += 1){
echo(mysql_result($result,$counter,"UserName")."<BR>");
}
mysql_close();
?>

So, if you prefer, you can use this.

[darrin365]

Thanks. However, I'm pretty sure mysql_select_db is merely for selecting the database you'll be working with. In my script it's already connected. And mysql_numrows is handy for counting the rows in your results, but I don't need to do that either.

Thanks for the help though.

[darrin365]

I'm closer. After some sleep, I realized that I had FROM dtd_trip2user in the SQL query twice. Took it out and, while reading through it, realized that the query wasn't correct. It never compared the table data against the current user's ID to pull his trips. So, I rewrote it to:
[code]SELECT dtd_trips.trip_name FROM dtd_trips, dtd_trip2user
WHERE dtd_trips.trip_id = dtd_trip2user.trip_id
AND dtd_trip2user.user_id = $userid;[/code]
thanks to a nice tutorial here -> [url=http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/]http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/[/url].

Now, I'm getting the correct data, but in a weird format.
Array ( [0] => Darrin's Test Trip [1] => Darrin's Test Trip 3 )

Gotta figure out how to get it to display:
Darrin's Test Trip
Darrin's Test Trip 3

[darrin365]

How now brown cow! I finally figured it out.
[code]$query = "SELECT dtd_trips.trip_name FROM dtd_trips, dtd_trip2user"
."\n WHERE dtd_trips.trip_id = dtd_trip2user.trip_id"
."\n AND dtd_trip2user.user_id = $userid"
;
$database->setQuery( $query );
$rows = $database->loadResultArray();
foreach ($rows AS $row) {
echo $row, "<br />";
} [/code]

Thanks to all for your assistance!