edit query union all

[helenp]

Hi,
I don´t know if this is posible, but maybe there are some way.
I have an union all like this:
$result = mysql_query ("(SELECT bookings.id etc FROM bookings)
UNION ALL
(SELECT bookings.id,etc WHERE limpiezas.id_reserva = bookings.id)
UNION ALL
(SELECT bookings.id, etc FROM bookings, pack WHERE pack.id_reserva = bookings.id)
UNION ALL
ORDER BY llegada;
", $dbh);
if ($row = mysql_fetch_array($result)){
echo "<table border = '1'> \n";
echo "<tr> \n";
echo "<td><b>Idr</b></td> \n";

It works just perfect, but the problem is I would like to be able to edit the diferents tables from here, and when I put an image with an link to one of the editpages, of course that image appears on all rows, I would like one image/link to appear in rows select 1, and another to appear in rows select 2 etc.
Any ideas?

[SofterLogic UK]

If i were you, use two handles, and run 2 queries

[coopster]

It sounds more like a formatting question rather than a query question. Can you give us an example layout of what you mean? Nothing fancy, just an idea. Are you saying you want something like...

id1 id2 id3 submitbutton1 
id4 id5 id6 submitbutton2 
id7 id8 id9 submitbutton1 
... 

?

[helenp]

example layout,
id.table1, date, name, submitbutton¿id_table1=$row["id"]- link page 1
id.table2, date, name, submitbutton¿id_table2=$row["id"] - link page 2
id.table3, date, name, submitbutton¿id_table3=$row["id"]- link page 3
Thanks

[helenp]

Aren´t there any way adding images to an table with an link and putting the image to the query?

Or maybe doing virtual columns with an link?

Maybe put an link to one of the results in the query?

I am new, and often things can be done one think you can´t.

I been trying with images but no luck

[coopster]

I'm not sure if I quite understand the question yet, but I'll go with what I think you are asking. A foreach [php.net] control structure would allow you to loop through the result set returned by your query, one line at time. Within that control structure you could easily add another column to each row of your table that builds the submit button, whether that be an image or a link.

[helenp]

maybe the answer is there, I havn´t studied foreach yet, seems complicated.
I tried this and works if only use one select, but when using two don´t work:
$links = $link;
$links = $links2;
$link = "<a href=\"edit_reservas.php\">llegada</a>";
$link2 = "<a href=\"edit_limpiezas.php\">llegada</a>";
$result = mysql_query("(SELECT bookings.id, llegada, tipo = '$link', cliente FROM bookings)
UNION ALL
(SELECT limpiezas.id_limpieza, fecha, limpiezas.tipo = '$link2', bookings.cliente FROM bookings, limpiezas WHERE limpiezas.id_reserva = bookings.id)
ORDER BY llegada, tipo;
", $dbh);

if ($row = mysql_fetch_array($result)){
echo "<td><a href=".$links.">".$row["tipo"]."</a></TD>\n";

With this I guess you can see what I mean.

[helenp]

One that know mysql, but don´t know php told me that I need an virtual column to determine from which select the result row comes from, and then use the php to test the value.

So now I have this selects that works perfect:
$result = mysql_query("(SELECT 1 as iden, bookings.id, etc FROM bookings)
UNION ALL
(SELECT 2 as iden2, limpiezas.id_limpieza, etc FROM limpiezas)
ORDER BY llegada, tipo;
", $dbh);

this uses foreach to display the result, code between /// is what I want to do, but of course doesn´t work:
echo "<table>\n";
while ($linea = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($linea as $valor_col) {

///if (isset($iden)){
if ($iden=="1"){
$link = "<a href=\"edit_reservas.php?\">Edit</a>";}
elseif ($iden=="2"){
$link = "<a href=\"edit_limpiezas.php?\">Edit</a>";}
}///

echo "\t\t<td>$valor_col</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";

[helenp]

Must be something with mysql_fetch_object.
I need to fetch the particular rows.

If I put this after the query the iden and iden2 are printed on top but of course I get an error on the table that is an fetch_array:
while ($row1 = mysql_fetch_object($result)) {
echo $row1->iden;
echo $row1->iden2;
}
mysql_free_result($result);
if ($row = mysql_fetch_array($result)){
echo "<table border = '1'> \n";

Any ideas

[helenp]

I think I got it, after 6 days.....lol.

Only one problem, small I hope,
the id isn´t passed by in this:

$row->iden = "<a href=\"edit_limpiezas_diario.php?id_limpieza=".$row["id"]."\">Edit</a>";}

Could this been done with fetch_array?
or is that the diference between them.

Final code
?>
<Table>
<tr>
<td>Edit</td>
<td>Id</td>
</tr>
<?php

while ($row = mysql_fetch_object($result))
{
if ($row->iden=="1"){
$row->iden= "<a href=\"edit_reservas.php?\">Edit</a>";}
elseif ($row->iden=="2"){
$row->iden = "<a href=\"edit_limpiezas_diario.php?id_limpieza=".$row["id"]."\">Edit</a>";}
echo "<tr>
<td>$row->iden</td>
<td>$row->id</td>
<td>$row->llegada</td>
<td>$row->tipo</td>
<td>$link</TD>
</tr>";
}
?>
</table>

[ergophobe]

You are mixing arrays and objects. Just stick with one or the other, so

$row->iden = "<a href=\"edit_limpiezas_diario.php?id_limpieza=".$row->id."\">Edit</a>";

That assumes, of course, that the code a few lines later is working

<td>$row->id</td>

if that works, then you have 'id', but you must access it as an object member, not an array element.

[helenp]

Thats works thanks a lot,

I didn´t know there were diferent for arrays and object, just searching and searching.
So I understand that arrays and object is the same, but with object you can point at an particular row?
Anyway it works.
All the manuals uses fetch_array.

[ergophobe]

An array is a data structure. All it can include is data and it's relatively limited.

An object is much more. It can include 'properties' that store data (similar to an array element) and 'methods' which are essentially functions that act on the object.

Confused? To get more confused, to a google search on "object oriented programming" ;-)

Tom

[helenp]

wow........
thanks.
Are there any plus or inconvinience of using on or another, I think I could perfectly use mysql_fetch_object on other pages with queries instead of fetch_array.

As well there are another thing I want to do, as having diferent backgrounds to rows depending on the queries result, I think that can be done using fetch_object,