| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-01-08, 15:08
|
|
Registered User
|
|
Join Date: May 2007
Posts: 139
|
|
|
or I must use absolutelly MYSQL VIEWS TECHNOLOGY in this case ?
|
|
Code:
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 5 in /home/content/p/o/l/polisch123/html/administration/bookings.php on line 121
MAY USE(PHP/MYSQL 5): a query to get results (SELECT) and while using a WHILE Statement to appear results (in the middle of this while statement) to use another query and appear results from another table using as PK the FK of the first table(outer query) ??? or I must use absolutelly MYSQL VIEWS TECHNOLOGY in this case ?
look code below can be done ?
Code:
$query = "SELECT * FROM $BookingsTable WHERE dated>='$today' ORDER BY dateplaced DESC"; // dated AND dated>='$today'"; email='$email6' AND status!='canceled' // appear bookings dated>=time() AND time()<='$dated'canceled
$result = @mysql_query($query,$linkid);
$count = @mysql_num_rows($result);
echo " ( Your query returned " . $count . " items. )";
?>
</caption>
<tr align="center" valign="middle">
<td width="5%">Name</td>
<td width="6%">Mob</td>
<td width="5%">Tel</td>
<td width="7%">Email<br />
(link to Cust. A/C) </td>
<td width="3%">BID</td>
<td width="4%">DATE</td>
<td width="4%">STATUS</td>
<td width="8%">PICKUP</td>
<td width="8%">DROPOFF</td>
<td width="2%">DAYS</td>
<td width="3%">RATE</td>
<td width="4%">CARGROUP</td>
<td width="7%">OFF[20%]</td>
<td width="6%">VAT[15%]</td>
<td width="9%">TOTAL[PAID]AMOUNT</td>
<td width="9%">Cancel / Confirm BID </td>
<td width="10%">BID</td>
</tr>
<?
// $type
$i=0;
if ($count > 0) {
while ($i < $count) {
$email = mysql_result($result,$i,"email");
$bookingid = mysql_result($result,$i,"bookingid");
$dateplaced = mysql_result($result,$i,"dateplaced");
$status = mysql_result($result,$i,"status");
$datep = mysql_result($result,$i,"datep");
$dated = mysql_result($result,$i,"dated");
$Locationp1 = mysql_result($result,$i,"Locationp");
$Locationd = mysql_result($result,$i,"Locationd");
$days = mysql_result($result,$i,"days");
$mostdaysrate = mysql_result($result,$i,"mostdaysrate");
$vat = mysql_result($result,$i,"vat");
$off = mysql_result($result,$i,"off");
$total = mysql_result($result,$i,"total");
$cat = mysql_result($result,$i,"cat");
// $bookingidarray=array();
//$cb=0;
$query1 = "SELECT * FROM $CustomersTable WHERE email='$email'"; // AND dated>='$today'"; email='$email6' AND status!='canceled' // appear bookings dated>=time() AND time()<='$dated'canceled
$result1 = @mysql_query($query1,$linkid);
$name = mysql_result($result1,$i,"name");
$mob = mysql_result($result1,$i,"mob");
$fixed = mysql_result($result1,$i,"fixed");
?>
<tr align="center" valign="middle">
<td><?=$name; ?></td>
<td><?=$mob; ?></td>
<td><?=$fixed; ?></td>
<td><a href="linktocustomer-ac.php?email=<?=$email; ?>" target="_blank">
<?=$email; ?></a></td>
<td><?=$bookingid; ?></td>
<td><?=date("H:i D d M y", $dateplaced);//$dateplaced; ?></td>
<td><?=$status; ?></td>
<td><?=date("H:i D d M y", $datep);//$datep;"H:i M d, y" ?><br />
from <?=$Locationp1; ?>
</td>
<td><?=date("H:i D d M y", $dated);//$dated; ?><br />
to <?=$Locationd; ?></td>
<td><?=$days; ?></td>
<td>€<?=$mostdaysrate; ?></td>
<td><?=$cat; ?></td>
<td>€<?=$off; ?></td>
<td>€<?=$vat; ?></td>
<td>€<?=$total; ?></td>
<td><input name="c[]" type="checkbox" id="c[]" value="<?=$bookingid; ?>" /></td>
<td><?=$bookingid; ?></td>
</tr>
<?
++$i;
mysql_free_result($result1);
} # end while
|
|
11-01-08, 15:23
|
|
Registered User
|
|
Join Date: May 2007
Posts: 139
|
|
|
see attached screenshot
|
11-01-08, 16:51
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
|
|
no, instead of doing a second query inside a while loop, you should be doing a single join query
|
|
11-01-08, 18:59
|
|
Registered User
|
|
Join Date: May 2007
Posts: 139
|
|
by saying join you mean MYSQL VIEW like below or ... INNER JOIN ?
Code:
CREATE VIEW Name_View (alias names _ same in number as fields of view_comma separated) AS
SELECT TABLENAME1.FIELD1, TABLENAME1.FIELD3, TABLENAME1.FIELD5, TABLENAME2.FIELD1, TABLENAME2.FIELD6, TABLENAME2.FIELD7
FROM TABLENAME1, TABLENAME2
WHERE TABLENAME1.FIELD1 = TABLENAME2.FIELD1;
|
|
11-01-08, 22:01
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
a view is a view, and doesn't always involve a join
i did mean inner join
|
|
11-02-08, 11:59
|
|
Registered User
|
|
Join Date: May 2007
Posts: 139
|
|
|
finally I used successfully this mysql VIEW code with one error
finally I used successfully this mysql VIEW code with one error: I and entries with $CustomersTable.status = 'deleted' rather than the wanted ONLY entries with $CustomersTable.status = 'active', where is the problem ?
as you see in the attachment there are two BID=6 one with a/c status=deleted and one with status=active ...well ?
Code:
$query_view = "CREATE VIEW admin_bookings (name,mob,fixed,email,bookingid,dateplaced,status,datep,dated,Locationp,Locationd,days,mostdaysrate,vat,off,total,cat) AS
SELECT $CustomersTable.name,$CustomersTable.mob,$CustomersTable.fixed,$CustomersTable.email,$BookingsTable.bookingid,$BookingsTable.dateplaced,$BookingsTable.status,$BookingsTable.datep,$BookingsTable.dated,$BookingsTable.Locationp,$BookingsTable.Locationd,$BookingsTable.days,$BookingsTable.mostdaysrate,$BookingsTable.vat,$BookingsTable.off,$BookingsTable.total,$BookingsTable.cat
FROM $CustomersTable, $BookingsTable
WHERE $CustomersTable.email = $BookingsTable.email AND $CustomersTable.status = 'active' ";
|
|
11-02-08, 12:05
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
i don't know what you're asking
your view will show only active rows
so your screenshot is not being produced from your view
|
|
11-02-08, 12:17
|
|
Registered User
|
|
Join Date: May 2007
Posts: 139
|
|
MORE CODE:
Code:
include("../dbinfo.php");
$linkid = mysql_connect($hostname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database"); // @
// Base query
$today = time()-30*24*60*60;
$query ='';
// $email6=$_SESSION['email']; //from above non needed
if (($_GET['orderby'] == "") && ($_SESSION['orderby'] == "")) {
$_SESSION['orderby'] = "dateplaced DESC";
$orderby = $_SESSION['orderby'];
} else if (($_GET['orderby'] != "") && (($_SESSION['orderby'] == "") || ($_SESSION['orderby'] != ""))) {
$orderby = $_GET['orderby'];
$_SESSION['orderby'] = $orderby;
} else if (($_GET['orderby'] == "") && ($_SESSION['orderby'] != "")) {
$orderby = $_SESSION['orderby'];
}
// $query = "SELECT * FROM $BookingsTable WHERE dated>='$today' ORDER BY $orderby "; // dateplaced dated AND dated>='$today'"; email='$email6' AND status!='canceled' // appear bookings dated>=time() AND time()<='$dated'canceled
// $result = @mysql_query($query,$linkid);
// -----------
$active = 'active';
$query_view = "CREATE VIEW admin_bookings (name,mob,fixed,email,bookingid,dateplaced,status,datep,dated,Locationp,Locationd,days,mostdaysrate,vat,off,total,cat) AS
SELECT $CustomersTable.name,$CustomersTable.mob,$CustomersTable.fixed,$CustomersTable.email,$BookingsTable.bookingid,$BookingsTable.dateplaced,$BookingsTable.status,$BookingsTable.datep,$BookingsTable.dated,$BookingsTable.Locationp,$BookingsTable.Locationd,$BookingsTable.days,$BookingsTable.mostdaysrate,$BookingsTable.vat,$BookingsTable.off,$BookingsTable.total,$BookingsTable.cat
FROM $CustomersTable, $BookingsTable
WHERE $CustomersTable.email = $BookingsTable.email AND $CustomersTable.status = $active ";
// ----------
$result_view = @mysql_query($query_view,$linkid);
$query = "SELECT * FROM admin_bookings WHERE dated>='$today' ORDER BY $orderby "; // dateplaced dated AND dated>='$today'"; email='$email6' AND status!='canceled' // appear bookings dated>=time() AND time()<='$dated'canceled
$result = @mysql_query($query,$linkid);
$count = @mysql_num_rows($result);
echo " ( Your query returned " . $count . " items. )";
?>
|
|
11-02-08, 12:55
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
sorry, this is the mysql forum, and i don't do php
do you want me to move the thread to the php forum?
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
or I must use absolutelly MYSQL VIEWS TECHNOLOGY in this case ?
