Zye
Posts: 56
|
| Posted: 09/13/2007, 11:04 AM |
|
CCS 3.2.0.2/PHP/MySql - XP pro
I have tried for hours to get this to work. Still learning. Can anyone see any error/s in my query/format or is there a bug with CCS3.2. I built the query with CCS query builder. When I run the code below there are no errors but also nothing is returned. I have run my SQL query in Navicat. It returned the desired results. Debug code in the label shows no results. I am trying to return all associated property_id's per user and list them as hyperlinks in a HTML label (propid).
Table 1: properties
PK: propety_id
Link: user_id
Table 2: users2
PK: user_id
Tried this code in Grid: before show, before show row.
-------------------------------------------------------------------------------------------------------------------------------
$db = new clsDBconnection();
$SQL = "SELECT users2.user_id AS users2_user_id, property_id, properties.user_id AS properties_user_id
FROM properties RIGHT JOIN users2 ON
properties.user_id = users2.user_id
WHERE properties.user_id =". users2.user_id;
$db->query($SQL);
$Result = $db->next_record();
if ($Result) {
$Prop_id = $db->f("property_id");
}
$db->close();
$users2->propid->SetValue("<a href=\"../proplist.php?property_id=" . $Prop_id . "\">" . $Prop_id . "</a><br>");
-------------------------------------------------------------------------------------------------------------------------------
Can anyone help please.
Cheers
Zye ...
|
 |
 |
wkempees
|
| Posted: 09/13/2007, 11:50 AM |
|
Your post is confusing to me.
First you state you have a grid and created an SQL through using
VisualQueryBuilder.
Next you post a BeforeShow SQL construct.
Is the grid supposed to have this SQL as its main SQL?
Then you do not need the BEforeShow, but mainly do everything in the grid's
VQB.
Dont worry about making any hard (href) changes, just first make the grid
show the record data.
When the grid is showing the approriate data, then you right-click the
property_id field and change it to being a link, setting the parameters
right will do all you describe in less than a minute.
Walter
|
|
|
|
Zye
Posts: 56
|
| Posted: 09/13/2007, 1:06 PM |
|
Hi Walter, thanks for your reply. After looking back at my post, your'e right, it is a bit confusing.
I created my query in VQB to use in my BeforeShow construct to acheive all the users property_id's in one table row cell.
I have already done what you have suggested and yes it works fine using the VQB on the grid and parameters on a link.
If the user has two or three properties (property_id) assosiated with them the grid makes three rows for the user. I want to have all associated properties in one cell/label without repeating the row i.e.
user_id | date Registered | first name | last name | email | associated properties (property_id) 10036 <br> 10037
Thanks
|
|
|
|
datadoit.com
|
| Posted: 09/13/2007, 1:40 PM |
|
Zye wrote:
> CCS 3.2.0.2/PHP/MySql - XP pro
>
> I have tried for hours to get this to work. Still learning. Can anyone see any
> error/s in my query/format or is there a bug with CCS3.2. I built the query with
> CCS query builder. When I run the code below there are no errors but also
> nothing is returned. I have run my SQL query in Navicat. It returned the desired
> results. Debug code in the label shows no results. I am trying to return all
> associated property_id's per user and list them as hyperlinks in a HTML label
> (propid).
>
> Table 1: properties
> PK: propety_id
> Link: user_id
>
> Table 2: users2
> PK: user_id
>
>
> Tried this code in Grid: before show, before show row.
>
> -------------------------------------------------------------------------------------------------------------------------------
>
> $db = new clsDBconnection();
> $SQL = "SELECT users2.user_id AS users2_user_id, property_id,
> properties.user_id AS properties_user_id
> FROM properties RIGHT JOIN users2 ON
> properties.user_id = users2.user_id
> WHERE properties.user_id =". users2.user_id;
>
> $db->query($SQL);
> $Result = $db->next_record();
> if ($Result) {
> $Prop_id = $db->f("property_id");
> }
> $db->close();
>
> $users2->propid->SetValue("<a href=\"../proplist.php?property_id=" . $Prop_id .
> "\">" . $Prop_id . "</a><br>");
>
> -------------------------------------------------------------------------------------------------------------------------------
>
> Can anyone help please.
> Cheers
> Zye ...
> ---------------------------------------
If this is in your BeforeShowRow, then I think your WHERE parameter in
your SQL should be something like:
WHERE properties.user_id='" . $Container->ds->f("user_id") . "'"
In fact, you don't even need the join. If your grid is built on users,
then your label control only needs:
$db->query("SELECT property_id FROM properties WHERE user_id='" .
$Container->ds->f("user_id") . "'");
while ($db->next_record())
{
$users2->propid->SetValue($users2->propid->GetValue() . "<a
href=\"../proplist.php?property_id=" . $Prop_id . "\">" . $Prop_id .
"</a><br>");
}
$db->close();
|
|
|
|
Zye
Posts: 56
|
| Posted: 09/13/2007, 3:47 PM |
|
Hi datadoit
Thanks alot for your help.
Your condensed query doesn't return any results at the moment. After playing around with it for a while I cannot figure out what is wrong with it.
<a href="../proplist.php?property_id="></a><br>
Your WHERE statement works (Yay!) but only returns one property_id instead of the two in my test 'properties' table. So it looks like I need some kind of loop to get them both. I am not knowledgable enough to figure that part out either. At least I am glad it wasn't a CCS bug. Though I know there are a few at the moment 
$db->query($SQL);
$Result = $db->next_record();
if ($Result) {
$Prop_id = $db->f("property_id");
}
$db->close();
This part of the statement produces the user's first property_id 100603
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
$db->query($SQL);
while ($db->next_record())
{
$Prop_id = $db->f("property_id");
}
$db->close();
This statement with (while) produces the user's second property_id 100604 ????
Your condensed code would be much better though
Any help is much appreciated.
|
|
|
|
Zye
Posts: 56
|
| Posted: 09/13/2007, 3:49 PM |
|
Forgot to mention that I am using it in my grid {users2} BeforeShowRow event as you mentioned.
|
|
|
|
datadoit.com
|
| Posted: 09/13/2007, 5:12 PM |
|
Zye wrote:
> Hi datadoit
> Thanks alot for your help.
>
> Your condensed query doesn't return any results at the moment. After playing
> around with it for a while I cannot figure out what is wrong with it.
> <a href="../proplist.php?property_id="></a><br>
>
> Your WHERE statement works (Yay!) but only returns one property_id instead of
> the two in my test 'properties' table. So it looks like I need some kind of loop
> to get them both. I am not knowledgable enough to figure that part out either.
> At least I am glad it wasn't a CCS bug. Though I know there are a few at the
> moment
>
> $db->query($SQL);
> $Result = $db->next_record();
> if ($Result) {
> $Prop_id = $db->f("property_id");
> }
> $db->close();
> This part of the statement produces the user's first property_id 100603
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> $db->query($SQL);
> while ($db->next_record())
> {
> $Prop_id = $db->f("property_id");
> }
> $db->close();
> This statement with (while) produces the user's second property_id 100604 ????
>
> Your condensed code would be much better though
> Any help is much appreciated.
> ---------------------------------------
Make certain that your variable is incremented and not replaced, either via:
$Prop_id = $Prop_id . $db->f("property_id");
or
$Prop_id .= $db->f("property_id");
The while() is the loop through each record. The reason you pulled the
second value is because that's the last value it found. Your variable
is getting overwritten on each loop.
|
|
|
|
Zye
Posts: 56
|
| Posted: 09/13/2007, 5:49 PM |
|
Hi datadoit
Brilliant! Many thanks.
I have one more question if you please.
The code now returns both values but they are now merged together like 100603100604.
How do I seperate the values?
Thanks
|
|
|
|
datadoit.com
|
| Posted: 09/13/2007, 6:51 PM |
|
Zye wrote:
> Hi datadoit
>
> Brilliant! Many thanks.
> I have one more question if you please.
>
> The code now returns both values but they are now merged together like
> 100603100604.
> How do I seperate the values?
>
> Thanks
> ---------------------------------------
Perhaps add a before and/or after the <br> tag, or try <br />.
|
|
|
|
Zye
Posts: 56
|
| Posted: 09/13/2007, 9:47 PM |
|
Hi datadoit and Walter
Thanks for all your help. I have solved my problem now, with some code borrowed from these forums a while back. I have adapted it and all is working now. Here is the result for anyone who needs it. If it wasn't for datadoit's WHERE clause I would be still stuck in my warp. Cheers!
Lots more for me to learn now. Especially loops and num_rows 
-------------------------------------------------------------------------------------------------------------
$columns = 1;
$db = new clsDBconnection();
$SQL = "SELECT users2.user_id AS users2_user_id, property_id, properties.user_id AS properties_user_id
FROM properties RIGHT JOIN users2 ON
properties.user_id = users2.user_id
WHERE properties.user_id='" . $users2->ds->f("user_id") . "'";
$result = mysql_query($SQL);
$num_rows = mysql_num_rows($result);
$rows = ceil($num_rows / $columns);
while($row = mysql_fetch_array($result)) {
$data[] = $row['property_id'];
}
for($i = 0; $i < $rows; $i++) {
$cid = $row['property_id'];
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
$grid .="<a href=\"../proplist.php.php?property_id=" . $data[$i + ($j * $rows)] . "\">" . $data[$i + ($j * $rows)] . "</a><br />\n";
}
}
}
$users2->propid->SetValue($grid);
-------------------------------------------------------------------------------------------------------------
Thanks again 
Zye ...
|
|
|
|
