|
PHP - Create A Relation Between Two Tables Mysql
i have two tables, employee details table(empno, emp_name , branch) and attendance table(date, Ot, attendence). I want to create a relation between two tables Similar TutorialsThis portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization) users: id / username / password / realname / access_level users_access_level: access_level / access_name What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries. Example row for users: 1234 / tmac / password / tmac / 99 Example row for users_access_level: 99 / Admin Using the examples above, I would want the output to appear as such: Username: Access Name: Tmac Admin I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction. The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself. Thanks for reading! I am trying to create seperate tables from this. Maybe a div class will work. I want each cat variable 'catname' to be in seperate tables at the top of each table with the subcats for that cat in 1 or 2 rows under the cat. I want to be able to also choose whether to use 1 or 2 table rows for the subcats under the cat. any ideas? Will appreciate your help. // Categories $sql = "SELECT catid, catname AS catname FROM $t_cats WHERE enabled = '1' $sortcatsql"; $rescats = mysql_query($sql) or die(mysql_error()); $catcount = @mysql_num_rows($rescats); $percol_short = floor($catcount/$dir_cols); $percol_long = $percol_short+1; $longcols = $catcount%$dir_cols; $i = 0; $j = 0; $col = 0; $thiscolcats = 0; while($rowcat=mysql_fetch_array($rescats)) { if ($j >= $thiscolcats) { $col++; $thiscolcats = ($col > $longcols) ? $percol_short : $percol_long; $j = 0; echo "<td valign=\"top\" width=\"$cell_width%\">"; } $i++; $j++; $catlink = buildURL("ads", array($xcityid, $rowcat['catid'], $rowcat['catname'])); $adcount = 0+$catadcounts[$rowcat['catid']]; ?> <table border="0" cellspacing="0" cellpadding="0" width="100%" class="dir_cat"> <tr> <th width="25" valign="top"><img src="images/category.gif" border="0" align="absmiddle"></th> <th><a href="<?php echo $catlink; ?>"><?php echo $rowcat['catname']; ?></a> <?php if($show_cat_adcount) { ?><span class="count">(<?php echo $adcount; ?>)</span><?php } ?> </th> </tr> <?php $sql = "SELECT scat.subcatid, scat.subcatname AS subcatname FROM $t_subcats scat WHERE scat.catid = $rowcat[catid] AND scat.enabled = '1' $sortsubcatsql"; $ressubcats = mysql_query($sql) or die(mysql_error()."<br>$sql"); $subcatcount = mysql_num_rows($ressubcats); while ($rowsubcat = mysql_fetch_array($ressubcats)) { if ($shortcut_categories && $subcatcount == 1 && $rowsubcat['subcatname'] == $rowcat['catname']) { continue; } $adcount = 0+$subcatadcounts[$rowsubcat['subcatid']]; $subcat_url = buildURL("ads", array($xcityid, $rowcat['catid'], $rowcat['catname'], $rowsubcat['subcatid'], $rowsubcat['subcatname'])); ?> <tr> <td> </td> <td> <a href="<?php echo $subcat_url; ?>"><?php echo $rowsubcat['subcatname']; ?></a> <?php if($show_subcat_adcount) { ?><span class="count">(<?php echo $adcount; ?>)</span><?php } ?> <br> </td> </tr> <?php } ?> </table> <br> <?php if($j==$thiscolcats || $i==$catcount) echo "</td>"; } ?> </tr></table> Hello, well I guess my question is pretty clear. How would I be able to read data out of an Excel sheet? F.E. to allow my users to simply upload an .xls file to help them avoid the coding and such. Of course, I know how to create the tables etc, the main problem is just getting that data. Also, I am uncertain if uploading an .xls file is a lot harder/totally done differently then images, and if so.. what is the best way to do this? P.s. I do want all the fancy equations to be included, just normally entered data (which would just follow some sort of a protocol). Thanks in advance. I am submitting $_POST data to a results page. The data comprises of references and text. Each reference has its own table on the results page. I need to check if an any of the table id's on the results page are the same as any of the references in $_POST, if any are, I am appending to the table, if not I am creatig a new table for the reference. How do I check if a table id exists in order to append, then create tables for those that don't Any help/guidance appreciated. Hi,
I am a bit of a MYSQL newbie so please bear with me. I have a database created about football/soccer stats. The database at the moment contains the following tables but more maybe added:
Players
Clubs
Seasons
Competitions
I will create php pages for these tables that add, edit and delete records for each. But the problem I can't get my head around is how to add a record that uses data from other tables, for example we would add a...
player -> club-season-competition
Lets say we want create a player called 'Joe Bloggs' and want to add his details, we need somehow for the page to display an option for club, season and competition. Maybe they can be drop down boxes but how does one fill those drop down boxes with records that are already added in the database for each table?
Thanks
Here is function that backup mysql database. It's working fine, but I want to download file instead of creating it. /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = str_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } //save file $date= date("d-M-Y_h-i"); $handle = fopen('Backup-'.$date.'.sql','w+'); fwrite($handle,$return); fclose($handle); } Usage: Quote backup_tables('HOST','USER','PASS','TABLE'); But this will create backup file on server. I also tried to add following code in function (at the end): echo $return; header('Content-type: text/plain'); header("Content-Disposition: attachment; filename=\"$filename\""); How to just download file, without showing it on screen instead of using fwrite/fclose function? Ohhh, these thoughts just run through my head as I sleep. But will it work? Can I set a function so that my UPDATE form will NOT execute after a certain DATE (or even a time of day?) I'm thinking something along the lines of: $time=timestamp if $time>19:00 echo" You need to make changes before 7:PM"; else (UPDATE... yadda yadda yadda) Yes, i know this is worse than a rough draft, but will it work? Is it acceptable methodology? I have a database table of plants, one of chemicals, one of products, one of illnesses. Each table is connected, for example lets say opium poppies. They contain the chemical morphine. Morphine is used in painkiller products. Its used to treat pain and other illnesses. On the opium poppy page, I want to list all the chemicals found in the poppy. All the products (i.e. opium, laudanum) made from the poppy. All the illnesses which are treated with the poppy. Users need to be able to edit this page, and enter chemicals, products or illnesses that are related to this plant. They also need to be able to edit the info on the page.
Making database relations is a pain in the ass with CakePHP. I don't want to waste any more time so I'm thinking maybe making it as a wordpress plugin is the way to go. How would you go about this? Is there an easier way than wordpress, like a better CMS, or framework? If I use wordpress, should I do all the DB interactions with AJAX to avoid having to interact with the DB through wordpress (this gave me massive trouble in the past since I had my data in an external db)?
My question is basically this: If I'm building a submission form - which places info into my DB via a mySQL query (INSERT INTO) - is there also a way to reference the id that it is creating? The form is set to just dump raw data into a database - each submission enters data into at least 8 DB tables which are all tied together by an $id row in each table. My query references those $id rows in each table to build a query. After each query is completed, a unique url (which is stated by the submitter in the form of /example/someotherword/) is generated. I need to find a way to place some sort of script within a page that displays the results based on each $id. So, lets say that the url is clicked, it would take you to a page that displays the results - but only does so based on the reference to that urls unique $id within the table. I'll provide code once I get home, but if you guys have any ideas that would be great. Hello guys, i want to create array from mysql what is the script for this...? value like $data = array("1" => .0032, "2" => .0028, "3" => .0021, "4" => .0033, "5" => .0034, "6" => .0031, "7" => .0036, "8" => .0027, "9" => .0024, "10" => .0021, "11" => .0026, "12" => .0024, "13" => .0036, "14" => .0028, "15" => .0025); I want to create a text file based blog ( not using MySQL or other databases). I have tried to google for tutorials but I couldn't find anything. is there anyone who can help me and give me some tips? is there any tutorials about this because I found nothing? Any good books? This is an assignment that I have to do and I am using this book: Spring into PHP 5 by Addison Wesley. I am a newbie in php and really want to do this because this is my last task and I really want to learn. So I'm grateful if anyone can help me. Hey guys, Currently Im using the code: while($row=mysql_fetch_array($nt)){ echo" $row[user_login],$row[user_email],$row[CallHistoryTimes],$row[CallHistoryInfo],$row[CallHistoryLastOp]$row[CallHistoryLastDate],<br>"; } To list a mysql array; what I am trying to do is to use the mysql row ID and turn the entire string above into a list. So for example, the database would get the ID information & create a link of edit.php/?id=3 If someone could help me on this it would be mega appreciated. Thanks What I mean by dynamic is say I created a function to generate a random code. I put it in a variable named $key. I make a form with a hidden input type with the name and value of $key. When the form posts, it generates the table name to whatever $key is. Because I tried something like this & its not querying. Heres my code: <?php $dbc = mysqli_connect('localhost', 'cpacrop_todo', 'pass', 'cpacrop_todo') or die('Failed to connect to database!'); function make_key($num_chars) { if ((is_numeric($num_chars)) && ($num_chars > 0) && (! is_null($num_chars))) { $key = ''; $accepted_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'; //seed srand(((int)((double)microtime()*1000003)) ); for ($i=0; $i<=$num_chars; $i++) { $random_number = rand(0, (strlen($accepted_chars) -1)); $key .= $accepted_chars[$random_number] ; } return $key; } } $key = make_key(6); ?> <form action="" method="post"> <input type="hidden" name="<?php echo "$key"; ?>" /> <input type="submit" name="submit" value="Submit" /> </form> <?php if ($_POST['submit'] == "Submit") { $query = "CREATE TABLE `$key` ( `id` INT AUTO_INCREMENT, `title` VARCHAR(35), `description` VARCHAR(365) );"; mysqli_query($dbc,$query) or die('Unable to query database.'); echo "Success"; } ?> If this is possible, what am I doing wrong? Thanks! Hey yall! I'm working on a new site idea and I've run across a problem that I know is simple enough but I'm stumped. It's in the signup form What I want to do is insert the new user into the 'Login' table and get the user id that was just created and use that to create a table with the user id in the name. Here is what I have: // now we insert user into 'Login' table mysql_real_escape_string($insert = "INSERT INTO `Login` (`UID`, `pass`, `HR`, `mail`, `FullName`) VALUES ('{$_POST['username']}', '{$_POST['pass']}', '{$_POST['pass2']}', '{$_POST['e-mail']}', '{$_POST['FullName']}')"); mysql_query($insert) or die( 'Query string: ' . $insert . '<br />Produced an error: ' . mysql_error() . '<br />' ); $error="Thank you, you have been registered."; setcookie('Errors', $error, time()+20); // Get user ID mysql_real_escape_string($checkID = "SELECT * FROM Login WHERE `mail` = '{$_POST['e-mail']}'"); while ($checkIDdata = mysql_fetch_assoc($checkID)) { $userID = $checkIDdata; // now we create table 'Transactions" for the user mysql_real_escape_string($create = "CREATE TABLE `financewatsonn`.`Transactions_{$userID}` ( `ID` INT( 20 ) NOT NULL AUTO_INCREMENT COMMENT 'Transaction ID', `name` VARCHAR( 50 ) NOT NULL COMMENT 'Name/Location', `amount` VARCHAR( 50 ) NOT NULL COMMENT 'Amount', `date` VARCHAR( 50 ) NOT NULL COMMENT 'Date', `category` VARCHAR( 50 ) DEFAULT NULL COMMENT 'Category', `delete` INT( 1 ) NOT NULL DEFAULT '0', UNIQUE KEY `ID` ( `ID` ) ) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT = 'User ID {$userID}'"); mysql_query($create) or die( 'Query string: ' . $create . '<br />Produced an error: ' . mysql_error() . '<br />' ); } I know in 'Get user ID' that I need to get the ID but I'm not sure how to get that information. i get the error Quote Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource at 166 which is the while line under Get user ID just trying to see if i have this right in my head here.. if i have a page that has a topic on it and many replies, the tables would look like this.. would this be the right code to pull out the topic AND all responses for say. www.mysite.com/view?id=1 $topic = 'SELECT * from topics where id = $id' $replies = 'SELECT * from replies where replies.topic_id = $id' I have a mysql table with the structure of Code: [Select] ID Menu_Name Parent_ID 1 Finance NULL 2 Business NULL 3 Investment 1 4 Trading 2 How can I create a html <ul><li> list based on the parent? Hi to everybody I need ur help because I’m trying to make a script in php to write the same data but with different date in MySQL depending on the splitting ($data06).... like a schedule...
For example if $data06 = annual and $data03 = “2019/01/01” programm must create in MySQL : 2019/01/01 2020/01/01 2021/01/01 2022/01/01 2023/01/01
if $data06=“half year” will create 10 date , increasing 6 moths ...
But I have a problem at finish of code switch ($data06) { case 'annual': $numrate = 5; $aumdata = "+12 months"; break; case 'half year': $numrate = 10; $aumdata = "+6 month"; break;
default: echo "error"; $sql = "INSERT into $nometb ( name, scadenza ) values ( '$data01', '$data03' )"; if ($conna->query($sql) === TRUE) {} else {die('ERROR'. $conna->error); }
//IMPORT NEXT AND NEW DATE $newDate = date_create($data03); for ($mul = 2; $mul <= $numrate; ++$mul) {
$datanuova = date_create($data03); $datanuova->modify($aumdata); $datanuova->format('yy/m/d'); $newDate = $datanuova->format('yy/m/d');
$sql = "INSERT into $nometb ( name, scadenza ) values ( '$data01', '$newDate' )"; if ($conna->query($sql) === TRUE) {} else {die('ERROR'. $conna->error); }
//HERE THERE IS ERROR $data03 = $newDate;
if ($conna->query($sql) === TRUE) {} else {die('ERRORE NELL\'IMPORTAZIONE'. $conna->error); } }
} }
}
How I can resolve ?
many thanks Francesco How would I create a prepared mysql temp table using PHP? I currently write my prepared statements like the example below. How would I create a "TEMP" table? Thanks!
$connection8y = new mysqli("host", "xxx", "xxx", "db"); |
|||||||