Complex Query with PHP

•

What is DaniWeb IT Discussion Community?

You're currently browsing the PHP section within the Web Development category of DaniWeb, a massive community of 402,862 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,021 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our PHP advertiser: Lunarpages PHP Web Hosting

Thread Tools Display Modes

Views: 1323 | Replies: 19 | Solved

Page 1 of 2

•

Join Date: Aug 2007

Posts: 154

Reputation: tanha is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

tanha

Offline

Junior Poster

Complex Query with PHP

Oct 10th, 2007

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:

Help with Code Tags

(Toggle Plain Text)

$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:

Help with Code Tags

(Toggle Plain Text)

<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...

•

Join Date: Aug 2007

Posts: 154

Reputation: tanha is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

tanha

Offline

Junior Poster

Re: Complex Query with PHP

Oct 10th, 2007

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...

•

Join Date: Aug 2007

Posts: 154

Reputation: tanha is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

tanha

Offline

Junior Poster

Re: Complex Query with PHP

Oct 11th, 2007

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

•

Join Date: Aug 2007

Location: Cavite,Philippines

Posts: 507

Reputation: ryan_vietnow is an unknown quantity at this point

Rep Power: 3

Solved Threads: 68

ryan_vietnow ryan_vietnow is offline

Offline

Posting Pro

Re: Complex Query with PHP

Oct 11th, 2007

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

"death is the cure of all diseases..."
http://ryantetek.wordpress.com

•

Join Date: Sep 2007

Location: Budapest

Posts: 251

Reputation: fatihpiristine has a little shameless behaviour in the past

Rep Power: 0

Solved Threads: 13

fatihpiristine fatihpiristine is offline

Offline

Posting Whiz in Training

Re: Complex Query with PHP

Oct 11th, 2007

it is not complicated but give more details.

•

Join Date: Aug 2007

Posts: 154

Reputation: tanha is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

tanha

Offline

Junior Poster

Re: Complex Query with PHP

Oct 17th, 2007

Help with Code Tags

(Toggle Plain Text)

<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>

<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

Attached Files

LIBRARY.zip (69.5 KB, 2 views)

•

Join Date: Sep 2007

Location: Budapest

Posts: 251

Reputation: fatihpiristine has a little shameless behaviour in the past

Rep Power: 0

Solved Threads: 13

fatihpiristine fatihpiristine is offline

Offline

Posting Whiz in Training

Re: Complex Query with PHP

Oct 17th, 2007

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

Do a favour, leave me alone

•

Join Date: Aug 2007

Posts: 154

Reputation: tanha is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

tanha

Offline

Junior Poster

Re: Complex Query with PHP

Oct 17th, 2007

Thanks for ur help...

•

Join Date: Sep 2007

Location: Budapest

Posts: 251

Reputation: fatihpiristine has a little shameless behaviour in the past

Rep Power: 0

Solved Threads: 13

fatihpiristine fatihpiristine is offline

Offline

Posting Whiz in Training

Re: Complex Query with PHP

Oct 17th, 2007

thank you for this kinda question... i needed to refresh my sql knowledge

i m almost donee

Do a favour, leave me alone

•

Join Date: Sep 2007

Location: Budapest

Posts: 251

Reputation: fatihpiristine has a little shameless behaviour in the past

Rep Power: 0

Solved Threads: 13

fatihpiristine fatihpiristine is offline

Offline

Posting Whiz in Training

Re: Complex Query with PHP

#10

Oct 17th, 2007

i did some changes and attached sql dump...

Attached Files

test.zip (4.3 KB, 7 views)

Do a favour, leave me alone

Page 1 of 2

•

Only community members can participate in forum threads. You must register or log in to contribute.

•

DaniWeb PHP Marketplace

•

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

•

Thread Tools	Display Modes
Show Printable Version Email this Page	Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

•

Similar Threads

Sending pic attachments via PHP form (PHP)
PHP Form, SQL connectivity issues, please help. (MySQL)
help with php and javascript (PHP)
Issue with the LIKE expression and php (PHP)
Please help me out with MySQL query (MySQL)
Help with a smyql query and php (MySQL)
php connection to mysql headache (PHP)
PHP/SQL query help (PHP)
I had a experience of a website coded in PHP (PHP)
Retreiving variables from a sql query into a form (PHP)

Other Threads in the PHP Forum

Previous Thread: 7x7 Forced Matrix Script
Next Thread: How to insert new line character

DaniWeb Home > Forums > Web Development > PHP

Complex Query with PHP

Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP

Re: Complex Query with PHP