Results 1 to 9 of 9
  1. #1
    Join Date
    May 2007
    Posts
    139

    Unanswered: 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&nbsp;<?=$Locationp1; ?>
                  </td>
                  <td><?=date("H:i D d M y", $dated);//$dated; ?><br />
    			  to&nbsp;<?=$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

  2. #2
    Join Date
    May 2007
    Posts
    139

    see attached screenshot

    see attached screenshot
    Attached Thumbnails Attached Thumbnails dbviews60.jpg  

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, instead of doing a second query inside a while loop, you should be doing a single join query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    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;

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a view is a view, and doesn't always involve a join

    i did mean inner join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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' ";
    Attached Thumbnails Attached Thumbnails view-adv.gif  

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    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. )";
    ?>

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, this is the mysql forum, and i don't do php

    do you want me to move the thread to the php forum?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •