php - Checking mysql insertion query -


i wrote query i'm having doubts.

the first 1 have insert cd supplied particular supplier , produced particular producer. 

$sql="insert cd (supplier_name,                        supplier_address,                        producer_name,                        producer_address,                        cd_title,                        cd_type,                        cd_year)               values ('$_post[supp_name]',                       '$_post[supp_addr]',                       '$_post[prod_name]',                       '$_post[prod_addr]',                       '$_post[cd_title]',                       '$_post[cd_year]' ,                       '$_post[cd_type]')";

but realized, have table called cd 3 attributes, title, year , type. cd(title, year, type). however, how need insert cd information particular supplier x , particular producer y. how do that?

enter image description here

also, form data entry looks this: 

<form action="cd.php" method="post"> <h4> enter cd information </h4>  cd title: <input type="text" name="cd_title"><br> cd year: <input type="text" name="cd_year"><br> cd type: <input type="text" name="cd_type"><br>  <h4>enter supplier information</h4> supplier name:  <input type="text" name="supp_name"><br> supplier address:<input type="text" name="supp_addr"><br>   <h4> enter producer information </h4>  producer name:<input type="text" name="prod_name"><br> roducer address:<input type="text" name="prod_addr"><br>  <input type="submit" name="submit" value="submit">

so, i'm wondering if user enters above data, stored? under table? (that is, supplier , producer info stored?

i'm not sure how mean, i'll give try:

try using select joins supplier , producer table:

select * cd join supplier s on cd.supplied = s.supplied join producer p on cd.produced = p.produced s.name = x , p.name = y

if above query returns true, insert.

update: when looking @ html-form think understand you're looking for...

you want relationsship between cd-table , producer-table , relationsship between cd-table , supplier-table.

you relation adding , supplier_id-column in cd-table , producer_id**-column in cd-table

and

an autoincremental id in both supplier-table , in producer-table.

the last step actual db to create constraints between supplier_id , id-column of supplier-table , producer_id , id-column of producer-table.

when you've done that, might this:

//when form submitted...   //first query  (insert if doesn't exist in supplier-table - ignore keyword takes care of this) $sql1="insert ignore supplier (supplier_name,                        supplier_address)               values ('$_post[supp_name]',                       '$_post[supp_addr]',                       )";  //execute query $sql1 //get last inserted id query $sql1 , store in $idlastinsertedsupplier   //second query  (insert if doesn't exist in producer-table - ignore keyword takes care of this)  $sql2="insert ignore producer (producer_name,                        producer_address)               values ('$_post[prod_name]',                       '$_post[prod_addr]')";  //execute query $sql2 //get last inserted id query $sql2 , store in $idlastinsertedproducer  //insert cd-table query:      $sql="insert cd (                           cd_title,                            cd_type,                            cd_year,                           supplier_id,                           producer_id                           )     values ('$_post[cd_title]',                           '$_post[cd_year]' ,                           '$_post[cd_type]', $idlastinsertedsupplier,$idlastinsertedproducer)";

of course should use prepared statements (with placeholders) pdo or mysqli. have not taken security of sql account (not using placeholder, not sanitizing data etc).

why id's? insert values 3 tables without having references (ids) between tables, there no point of having relational database. harder sql-operations afterward. let's want list of cd's producers , suppliers.

with relational storage (with id's) execute sql-statement this:

select * cd join supplier s on cd.id = s.id join producer p on cd.id = p.id

and fetch rows.

there no way of doing same thing without having these references. (it's impossible db know row in producer-table belongs row in cd-table without having reference). of course add name of producers , suppliers in cd-table also, there duplicates , of time don't want (in extreme cases speed option have these duplicates though).

i hope made clearer now.


Comments

Post a Comment

Popular posts from this blog

.htaccess - First slash is removed after domain when entering a webpage in the browser -

sometimes when hit address of webpage in browser, removes first slash after domain. examples: www.mywebsite.com/scripts/script_6.php -> trying load www.mywebsite.comscripts/script_6.php www.mywebsite.com/test.php -> trying load www.mywebsite.comtest.php after entering again, loads page. a .htaccess settings must wrong: rewritebase / errordocument 404 http://www.mywebsite.com/oops.php errordocument 401 /notallowed.php rewriteengine on # turn on rewriting engine #gzip <ifmodule mod_deflate.c> addoutputfilterbytype deflate text/text text/html text/plain text/xml text/css application/x- javascript application/javascript </ifmodule> #end gzip rewritecond %{http_host} ^mywebsite\.com rewriterule ^(.*)$ http://www.mywebsite.com$1 [r=permanent,l] directoryindex index6.php rewriterule ^termsofuse/?$ termsofuse.php [nc,l] # handle requests "adpolicy" rewriterule ^privacy-policy/?$ privacy-policy.php [nc,l] # handle requ...
Read more

Automatically create pages in phpfox -

i have array of information of places , wanna use them create multiple pages in phpfox site. first tried manually insert data in phpfox database (in tables of phpfox_pages , phpfox_pages_text ). the page shown in site when opening it's link, site says: the page looking cannot found. do know other tables should manipulate make work? or know plugin phpfox same? thanks, solved ( @purefan guide): "pages" has user if take @ phpfox_user table, you'll found out how fetch new records on that. there 2 ambiguous fields in table ( password & password_salt ). can use script create values fields: <?php function generaterandomstring() { $length = 164; $characters = '-_0123456789abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'; $randomstring = ''; ($i = 0; $i < $length; $i++) { $randomstring .= $characters[rand(0, strlen($characters) - 1)]; } return $randomstring; } $ssalt = '';...
Read more

c# - Farseer ContactListener is not working -

i'm using farseer in xna project, have trouble contactlistener. created class contactlistener these 2 error messages , don't know how fix problems. the type or namespace name 'contactlistener' not found (are missing using directive or assembly reference?) the type or namespace name 'contactimpulse' not found (are missing using directive or assembly reference?) what wrong contactlistener class? class mycontactlistener: contactlistener { void begincontact(contact contact) { /* handle begin event */ } void endcontact(contact contact) { /* handle end event */ } void presolve(contact contact, ref manifold oldmanifold) { fixture fixturea = contact.fixturea; fixture fixtureb = contact.fixtureb; if (fixtureb.collisioncategories == category.cat10) { contact.enabled = false; } } void postsolve(contact contact, ref contactimpulse impulse) { /* handle post-solve event */ } }...
Read more