close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

MySqli Help

Thread began 11/10/2014 9:58 am by The Flying Fox | Last modified 11/10/2014 12:47 pm by Jason Byrnes | 672 views | 4 replies |

The Flying Fox

MySqli Help

Hi,

We have an arena which we sell trade stand space to traders for the annual highland games. I want to allocate space to traders based on their space requirements

I have two dynamic select boxes on my page. These select boxes are for the Admin to allocate a location(pitch) that the client requires

Select One menu pulls info from two tables - the user table and product table. This then tells the admin what user requires what product!

i.e. User 15 requires product 8 - (product 8 is a pitch with the size of 15x15)

Select Two Menu pulls the info from a table that hold the pitch locations 40 pitches in total ranging in size based on the requirement for a particular year.

Once both fields have been selected this is then saved into a table with the traderID and pitchID ready for printing.

The thing I am struggling with is the SQLi code. In both select options I want the traderID and pitchID removed from the dropdowns once they have been allocated.

This did work in mySlq but I cannot get it to work in MySqli. below is my Sqlii code without the additional table. I can get it to show what pitches have been allocated but not the other way around.

<?php
$rsTraders = new WA_MySQLi_RS("rsTraders",$admin_chg,0);
$rsTraders->setQuery("SELECT tbl_traders.traderID, tbl_traders.trading_name, tbl_products.ProductSKU, tbl_products.ProductDimensions FROM tbl_traders LEFT OUTER JOIN tbl_products ON tbl_traders.pitch_req = tbl_products.ProductID WHERE tbl_traders.approved = 1");
$rsTraders->execute();
?>

this query gives all info

<?php
$rsTraders = new WA_MySQLi_RS("rsTraders",$admin_chg,0);
$rsTraders->setQuery("SELECT tbl_traders.traderID, tbl_traders.trading_name, tbl_products.ProductSKU, tbl_products.ProductDimensions, tbl_pitch_allocation.* FROM tbl_traders LEFT OUTER JOIN tbl_products ON tbl_traders.pitch_req = tbl_products.ProductID LEFT OUTER JOIN tbl_pitch_allocation ON tbl_traders.traderID = tbl_pitch_allocation.traderID WHERE tbl_traders.approved = 1");
$rsTraders->execute();
?>

I want to set it up so that the traderID is removed from the list if it exists in the table...I have been trying this for a few days with not luck.

New fresh eyes needed...I think I'm too close to the issue..

Thanks

Andy

Sign in to reply to this post

Jason ByrnesWebAssist

  This did work in mySlq  



what was the recordset to get this t work in MySQL?

the MySQLi recordset will be the same.

Sign in to reply to this post

The Flying Fox

Hi Jason,

That's the problem...I did have it working but when I upgraded I inadvertently deleted the recordset because I thought I could be smart and recreate without any problems.

I'm nearly there I just forgot the steps.

As the image attached shows, the last three columns are from the allocated table, with the others on the left coming from the other two tables and space where they have not been allocated yet.

Sorry to be a pain..

Andy

Sign in to reply to this post

The Flying Fox

Completed

Hi Jason,

I got there eventually

<?php
$rsTraders = new WA_MySQLi_RS("rsTraders",$admin_chg,0);
$rsTraders->setQuery("SELECT tbl_traders.traderID, tbl_products.ProductDimensions, tbl_traders.approved, tbl_traders.trading_name FROM (tbl_traders LEFT JOIN tbl_pitch_allocation ON tbl_traders.traderID = tbl_pitch_allocation.traderID) LEFT JOIN tbl_products ON tbl_traders.pitch_req = tbl_products.ProductID WHERE tbl_pitch_allocation.traderID Is Null and tbl_traders.approved=1;");
$rsTraders->execute();
?>

Thanks

Andy

Sign in to reply to this post

Jason ByrnesWebAssist

ok, glad to hear you found it.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...