954,561 Members — Technology Publication meets Social Media

Have something to say? Contribute New Article Reply to this Article

0

Complex Query with PHP

Hi.
I want to create a program for LIBRARY, I faced to problem on depositing book section (Giving book to someone), in this section there is a combo which should contian the books name, maybe we have from one book two, three (Quantity), if one of them given to some one then one should be less than Book Quantity, and if all of them given for deposit, then in the combo the name of that book must not appear, so I did this in MS ACCESS, now want to have this in PHP and MySQL, so I did the following but I dont know what is the problem:

$qryDepositedBooks = select("SELECT tblDeposit.bookId AS [Book ID], Last(tblDeposit.depositId) AS [Last Deposited Book], Count(tblDeposit.depositId) AS [Total Deposited Book], tblBook.bookQuantity, tblDeposit.isBookReturn
FROM tblBook LEFT JOIN tblDeposit ON tblBook.bookId = tblDeposit.bookId GROUP BY tblDeposit.bookId, tblBook.bookQuantity, tblDeposit.isBookReturn HAVING (((tblDeposit.isBookReturn)=0))");

 $qryDepositedBooksForPersonnel = select("SELECT tblDeposit.*
FROM $qryDepositedBooks LEFT JOIN tblDeposit $qryDepositedBooks.[Last Deposited Book] = tblDeposit.depositId");

 $qryBooksReadyForDeposit = select("SELECT tblBook.bookName, tblBook.bookId, $qryDepositedBooks.[Total Deposited Book], $qryDepositedBooksForPersonnel.isBookReturn FROM (tblBook LEFT JOIN $qryDepositedBooks ON tblBook.bookId = $qryDepositedBooks.[Book ID]) LEFT JOIN $qryDepositedBooksForPersonnel ON tblBook.bookId = $qryDepositedBooksForPersonnel.bookId
WHERE ((($qryDepositedBooks.[Total Deposited Book])<([tblBook]![bookQuantity]) Or ($qryDepositedBooks.[Total Deposited Book]) Is Null)) OR ((($qryDepositedBooksForPersonnel.isBookReturn)=1))
ORDER BY $qryDepositedBooks.[Total Deposited Book] DESC");

in combo section:

<td><select name="cboBook" id="cboBook">
    
   <option></option>
   <?php 
   for($i=0;$i<mysql_num_rows($qryBooksReadyForDeposit);$i++)
   {
    $b_code=mysql_fetch_assoc($qryBooksReadyForDeposit);
    ?>
    <option value="<?php print($b_code['bookId']); ?>"><?php print($b_code['bookName']); ?></option>
    <?php
   }
   ?>
    
     </select></td>

I cant see anything in Combo
need your guide...

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

I want to know how can I merge three queries together, if u see the above code, I have three queries, which I used one in other, How it is possible, plz some one guide me through this...

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

anyone plz help me through this problem, please...

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

Try the UNION function in SQL to merge 3 queries together..

Posting Pro

578 posts since Aug 2007

Reputation Points: 28

Solved Threads: 71

0

it is not complicated but give more details.

Posting Whiz in Training

283 posts since Sep 2007

Reputation Points: 6

Solved Threads: 19

0

<body>
<?php
 include ("../../ControlPanel/General/database.php");
   connect();
 $sqlpersonnel = select("SELECT personnelId, personnelCode, personnelName, personnelFamily FROM tblpersonnel ORDER BY personnelCode");
 //$sqlbook = select("SELECT bookId, bookCode, bookName  FROM tblbook ORDER BY bookCode");
 
 $qryDepositedBooks = select("SELECT tblDeposit.bookId AS [Book ID], Last(tblDeposit.depositId) AS [Last Deposited Book], Count(tblDeposit.depositId) AS [Total Deposited Book], tblBook.bookQuantity, tblDeposit.isBookReturn
FROM tblBook LEFT JOIN tblDeposit ON tblBook.bookId = tblDeposit.bookId GROUP BY tblDeposit.bookId, tblBook.bookQuantity, tblDeposit.isBookReturn HAVING (((tblDeposit.isBookReturn)=0))");
 $qryDepositedBooksForPersonnel = select("SELECT tblDeposit.*
FROM $qryDepositedBooks LEFT JOIN tblDeposit $qryDepositedBooks.[Last Deposited Book] = tblDeposit.depositId");
 $qryBooksReadyForDeposit = select("SELECT tblBook.bookName, tblBook.bookId, $qryDepositedBooks.[Total Deposited Book], $qryDepositedBooksForPersonnel.isBookReturn FROM (tblBook LEFT JOIN $qryDepositedBooks ON tblBook.bookId = $qryDepositedBooks.[Book ID]) LEFT JOIN $qryDepositedBooksForPersonnel ON tblBook.bookId = $qryDepositedBooksForPersonnel.bookId
WHERE ((($qryDepositedBooks.[Total Deposited Book])<([tblBook]![bookQuantity]) Or ($qryDepositedBooks.[Total Deposited Book]) Is Null)) OR ((($qryDepositedBooksForPersonnel.isBookReturn)=1))
ORDER BY $qryDepositedBooks.[Total Deposited Book] DESC");
 
 disConnect();
?>
 <form id="frmDefineDeposit" name="frmDefineDeposit" method="post" action="saveDeposit.php">
   <table width="1000" border="0" align="center" cellpadding="3" cellspacing="3">
  <tr>
    <td width="256"><label>Select Personnel Info :</label>
      &nbsp;</td>
    <td width="523"><select name="cboPersonnel" id="cboPersonnel">
 
   <option></option>
   <?php 
   for($i=0;$i<mysql_num_rows($sqlpersonnel);$i++)
   {
    $p_code=mysql_fetch_assoc($sqlpersonnel);
    $p_info = $p_code['personnelCode'] . " - " . $p_code['personnelName'] . " - " . $p_code['personnelFamily'];
    ?>
    <option value="<?php print($p_code['personnelId']); ?>"><?php print($p_info); ?></option>
    <?php
   }
   ?>   
 
      </select>
    <span class="style1">*</span></td>
  </tr>
  <tr>
    <td><label>Select Book  Info :</label>
      &nbsp;</td>
    <td><select name="cboBook" id="cboBook">
 
   <option></option>
   <?php 
   for($i=0;$i<mysql_num_rows($qryBooksReadyForDeposit);$i++)
   {
    $b_code=mysql_fetch_assoc($qryBooksReadyForDeposit);
    ?>
    <option value="<?php print($b_code['bookId']); ?>"><?php print($b_code['bookName']); ?></option>
    <?php
   }
   ?>
 
      </select>
    <span class="style1">*</span></td>
  </tr>
  <tr>
    <td><label>Date Received :</label>
      &nbsp;</td>
    <td><input name="txtDateReceived" type="text" id="txtDateReceived" size="40" />
       <span class="style1">*</span></td>
  </tr>
  <tr>
    <td><label>Date Returned   :</label>
      &nbsp;</td>
    <td><input name="txtDateReturned" type="text" size="40" id="txtDateReturned" /></td>
  </tr>
  <tr>
    <td><label>Select Type of Guarantee :</label>
      &nbsp;</td>
    <td><select name="cboGuarantee" id="cboGuarantee">
    <option></option>
      <option value="Library Card">Library Card</option>
      <option value="ID Card">ID Card</option>
      <option value="Student Card">Student Card</option>
      <option value="Passport">Passport</option>
      </select>
    </td>
     </tr>
  <tr>
    <td><label>Comment :</label>
      &nbsp;</td>
    <td><textarea name="txtComment" cols="80" rows="6" id="txtComment"></textarea></td>
     </tr>
  <tr>
    <td><input name="cmdDepositBook" type="button" class="cmd" id="cmdDepositBook" onclick="checkForm();" value="Deposit Book"/>
      <span class="style1">
      <input name="cmdBack" type="button" id="cmdBack" value="Back Main" onclick="window.location.href='../../ControlPanel/main.php';" class="cmd"/>
       </span></td>
    <td>&nbsp;</td>
  </tr>
   </table>
 </form>
<?php
 if(isset($_GET['OK']))
 {
  ?>
   <script language="javascript"> alert('Book deposited successfully...'); </script>
  <?php
 }
 if(isset($_GET['notOK']))
 {
  ?>
  <script language="javascript"> alert('Sorry, Book did deposited!!!'); </script>
  <?php
 }
?>
</body>

NOTE:
I want from those three queries, just the books which is ready for deposit shown in combo box, I did this in MS Access, but now want to migrate to PHP and MySQL, so plz someone help and guide me through this ...

I want to have the same queries of the attachment in php and MySQL

Attachments LIBRARY.zip (69.49KB)

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

ok. i will work on this tonight and let you know...

Posting Whiz in Training

283 posts since Sep 2007

Reputation Points: 6

Solved Threads: 19

0

Thanks for ur help...

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

thank you for this kinda question... i needed to refresh my sql knowledge :) i m almost donee

Posting Whiz in Training

283 posts since Sep 2007

Reputation Points: 6

Solved Threads: 19

0

i did some changes and attached sql dump...

Attachments test.zip (4.28KB)

Posting Whiz in Training

283 posts since Sep 2007

Reputation Points: 6

Solved Threads: 19

0

Thanks very much for doing that...I will test it and will post the result...

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

Mr. fatihpiristine, thank u much for the attachment, but there is a misunderstanding...I have the below problem...

I want to create a program for LIBRARY, I faced to problem on depositing book section (Giving book to someone), in this section there is a combo which should contian the books name, maybe we have from one book two, three (Quantity), if one of them given to some one then one should be less than Book Quantity, and if all of them given for deposit, then in the combo the name of that book must not appear, so I did this in MS ACCESS, now want to have this in PHP and MySQL, so I did the following but I dont know what is the problem:

$qryDepositedBooks = select("SELECT tblDeposit.bookId AS [Book ID], Last(tblDeposit.depositId) AS [Last Deposited Book], Count(tblDeposit.depositId) AS [Total Deposited Book], tblBook.bookQuantity, tblDeposit.isBookReturn
FROM tblBook LEFT JOIN tblDeposit ON tblBook.bookId = tblDeposit.bookId GROUP BY tblDeposit.bookId, tblBook.bookQuantity, tblDeposit.isBookReturn HAVING (((tblDeposit.isBookReturn)=0))");

$qryDepositedBooksForPersonnel = select("SELECT tblDeposit.* FROM $qryDepositedBooks LEFT JOIN tblDeposit $qryDepositedBooks.[Last Deposited Book] = tblDeposit.depositId");

$qryBooksReadyForDeposit = select("SELECT tblBook.bookName, tblBook.bookId, $qryDepositedBooks.[Total Deposited Book], $qryDepositedBooksForPersonnel.isBookReturn FROM (tblBook LEFT JOIN $qryDepositedBooks ON tblBook.bookId = $qryDepositedBooks.[Book ID]) LEFT JOIN $qryDepositedBooksForPersonnel ON tblBook.bookId = $qryDepositedBooksForPersonnel.bookId WHERE ((($qryDepositedBooks.[Total Deposited Book])<([tblBook]![bookQuantity]) Or ($qryDepositedBooks.[Total Deposited Book]) Is Null)) OR ((($qryDepositedBooksForPersonnel.isBookReturn)=1)) ORDER BY $qryDepositedBooks.[Total Deposited Book] DESC");

// $qryDepositedBooks, $qryDepositedBooksForPersonnel , $qryBooksReadyForDeposit as you see I created these queries, but I dont know how to use them with each other queries

in combo section:

<?php
for($i=0;$i<?php print($b_code['bookName']); ?>
<?php
}
?>

Just in this combo I want to see the bookName from $qryBooksReadyForDeposit, nothing more...

need your guide...

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

i fixed your code. what happened to that??

it was working.

Posting Whiz in Training

283 posts since Sep 2007

Reputation Points: 6

Solved Threads: 19

0

yeah it is working, but in the combo box it just shows the books which given to deposit, not the books which exist in Library...

if possible please make those above three queries which I posted before, and in the combo just displying the bookName from $qryBooksReadyForDeposit...
Thanks much

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

change sql query syntax...

Posting Whiz in Training

283 posts since Sep 2007

Reputation Points: 6

Solved Threads: 19

0

could you please be in more detail, because I am not so expert...I am here now...thanks

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

sorry.. i dont have time to code that pages again. i deleted them already. as far as i remember,
i saved a php file called readytodeposit, it lists the books which are ready to deposit.. ( and drops the numbers of deposited books from the initial quantity... // ig: u have 20 same books. each time it drops one. as you wanted. if the quantity to deposit = 0 then, you will not the name of the book.

i did everything in sql query if you read the codes you will understand.

your sql syntax has many problems... do not try to post such that sql query syntax via php files.. you ll get error..

Posting Whiz in Training

283 posts since Sep 2007

Reputation Points: 6

Solved Threads: 19

0

Ok, thanks anyway...I will go through that hardly, but could u please give the idea how to use one variable which has query result inside it, into another variable same as I did?
I mean how to merge one query inside another query???
That would be very nice of you...

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

Thanks very much Mr. fatihpiristine for the code, it is working very very good...I will post the code as soon as possible...Thanks again

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

0

Thanks a world from Mr. fatihpiristine providing this code for me, this is exactly what I wanted to do...Thanks again

<?php
	$qryDepositedBooks = select("
	
SELECT
tblbook.bookId,
tblbook.bookCode,
tblbook.bookStore,
tblbook.bookName,
lstsubject.subName,
tblbook.bookQuantity,
tblbook.bookQuantity - (select  count(tbldeposit.depositId) from tbldeposit where tblbook.bookId = tbldeposit.bookId ) as Deposited
from tblbook, lstsubject
where
tblbook.bookSubject = lstsubject.subId and
( tblbook.bookQuantity - (select  count(tbldeposit.depositId) from tbldeposit where tblbook.bookId = tbldeposit.bookId ) ) > 0
	
	
	
	");
?>

Posting Whiz in Training

218 posts since Aug 2007

Reputation Points: 8

Solved Threads: 1

This question has already been solved

You

© 2012 DaniWeb® LLC

Home | About Us | Contact Us | Terms of Service | Advertising Opportunities