php - Delete from inner join mysql query - two tables multiple conditions -


i have 2 mysql tables:

table allproducts   accountid, productowner, productnumber   100001, tom, abc1   100001, tom, abc2   100001, greg, abc3   100002, charlie, abc2    table productdata   accountid, productnumber, productdesc   100001, abc1, deschere   100001, abc2, deschere   100001, abc3, deschere   100002, abc2, deschere

i need delete productdata productnumbers same in both tables , specify variables accountid is, , productowner is.

e.g know accountid 100001 , productowner tom. therefore want rows 1 , 2 in productdata table deleted only.

edit: believe may have cracked query i've been working on

mysql_query("delete productdata.* productdata     inner join allproducts on productdata.productnumber = allproducts.productnumber       (productdata.accountid = '100001'         , allproducts.productowner = 'tom')");

i've done quick test , seems work - thoughts/criticisms?

your use of mysql_query deprecated of php 5.5.0 , removed in future. should start using mysqli or pdo_mysql extension.

i further suggest store query:

  delete productdata.* productdata     inner join allproducts on productdata.productnumber = allproducts.productnumber       (productdata.accountid = '100001'         , allproducts.productowner = 'tom'

in stored procedure on database.

when using pdo's example, can call follows:

    $db = new pdo('mysql:host=xxx;port=xxx;dbname=xxx', 'xxx', 'xxx', array( pdo::attr_persistent => false));      // sure cleanse passed in arguments!     $stmt = $db->prepare("call deleteproductdata($accountid, $productowner)");      // call stored procedure     $stmt->execute();

stored procedure example:

create definer=`root`@`localhost` procedure `deleteproductdata`(in `accountid` bigint, in `productowner` varchar(128))     language sql     not deterministic     contains sql     sql security definer     comment '' begin     delete productdata           inner join allproducts on productdata.productnumber = allproducts.productnumber           productdata.accountid = accountid , allproducts.productowner = productowner; end

this way moving mysql code out of php , database, belongs.


Comments

Post a Comment

Popular posts from this blog

user interface - Python attempting to create a simple gui, getting "AttributeError: 'MainMenu' object has no attribute 'intro_screen'" -

complete newbie here python started learning language few days ago (with no programming experience beforehand). i'm bashing skull against desk here, trying create 1 menu button lead menu, supposed replace/hide/destroy previous menu (either works, long process can reversed). what i've come far: import wx class mainframe(wx.frame): def __init__(self): wx.frame.__init__(self, none) self.centre() self.main_menu = mainmenu(self) self.intro_screen = introscreen(self) self.intro_screen.hide() class mainmenu(wx.frame): def __init__(self, parent): wx.frame.__init__(self, parent=parent) self.main_menu = mainmenu panel = wx.panel(self) sizer = wx.boxsizer(wx.vertical) nextscreen = wx.button(panel, label='next screen', size=(150,30)) nextscreen.bind(wx.evt_button, self.nextscreen) sizer.add(nextscreen, 0, wx.center|wx.all, 5) self.show() self.c...
Read more

jquery - Common JavaScript snippet to share files on Google Drive, Dropbox, Box.net or SkyDrive -

this first assignment of file sharing. need share files our website above mentioned locations. currently, have been exploring each service’s api file sharing. question 1 : there javascript code snippet available (like addthis.com ) share files? question 2 : should use server side coding file sharing or javascript advisable? thanks viewers. found useful plug-in named "ink file picker". saves given http file resource directly users online storage engine. url: https://developers.inkfilepicker.com/docs/web/ live example: http://ctrlq.org/save/
Read more

Android Gson.fromJson error -

i've completed tutorial how connect json url , parse content using gson library. code works fine url specified in tutorial, tryed modify work this url . tryed display 1 field second ulr, base field, can't work. here code, please help: public class jsonparsingactivity extends activity { string url = "http://api.openweathermap.org/data/2.5/weather?q=london,uk"; @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.main); inputstream source = retrievestream(url); gson gson = new gson();// main gson class reader reader = new inputstreamreader(source); searchresponse response = gson.fromjson(reader, searchresponse.class); //gson.fromjson(string, class) toast.maketext(this, response.base, toast.length_short).show(); } private inputstream retrievestream(string url) { defaulthttpclient client = new defaulthttpclient(); httpget getrequest = new httpget(url);...
Read more