PHP - Join And Use Unique Data From Same Column Names
I have data similar to below. I am doing a MySQL query and doing a JOIN based on instructor name. I am echoing classes and linking the instructor name to another page with their BIO. All is working great, however I want to use the ID's for something else. How do I differentiate the ID between the Instructors table and the Classes Table without changing the column name in the database?
For instance, I want to echo the Instructor ID and then echo the Classes ID, but they are both the same column name in the database and since they are joined, how will that work?
Instructors:
ID | Instructor | Title | Bio |
1 | Bob Smith | Chef | bob smith is a chef with... |
2 | Jane Doe | Professor | Jane doe is an instructor that... |
Classes:
ID | Instructor | Class Title | Class Description |
4 | Bob Smith | Baking | this is a baking class where you learn.... |
5 | Jane Doe | Food Safety | Food safety is very important... |
Similar TutorialsMy Php Buddies, I have mysql tbl columns these:
id: Now, I want to display their row data by excluded a few columns. Want to exclude these columns: date_&_time account_activation_code account_activation_status id_verification_video_file_url password
So, the User's (eg. your's) homepage inside his account should display labels like these where labels match the column names but the underscores are removed and each words' first chars CAPITALISED:
Id: 1
For your convenience only PART 1 works. Need help on Part 2 My attempted code:
PART 1 <?php // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Query to get columns from table $query = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'"); while($row = $query->fetch_assoc()){ $result[] = $row; } // Array of all column names $columnArr = array_column($result, 'COLUMN_NAME'); foreach ($columnArr as $value) { echo "<b>$value</b>: ";?><br><?php } ?> PART 2 <?php //Display User Account Details echo "<h3>User: <a href=\"user.php?user=$user\">$user</a> Details</h3>";?><br> <?php $excluded_columns = array("date_&_time","account_activation_code","account_activation_status","id_verification_video_file_url","password"); foreach ($excluded_columns as $value2) { echo "Excluded Column: <b>$value2</b><br>"; } foreach ($columnArr as $value) { if($value != "$value2") { $label = str_replace("_"," ","$value"); $label = ucwords("$label"); //echo "<b>$label</b>: "; echo "$_SESSION[$value]";?><br><?php echo "<b>$label</b>: "; echo "${$value}";?><br><?php } } ?> PROBLEM: Columns from the excluded list still get displayed. Edited November 19, 2018 by phpsaneOkay, so I'm kind of a PHP noob.
But out of context, for this site that I'm designing, it's easiest if I make a directory in the temporary folder PHP uses. In my case, /tmp/ because I am on Linux.
I want to use rand() to generate a random name for the page. But I then realized something, rand() could produce duplicates. How do I prevent PHP from trying to make the same directory at the same time? I know there are functions that will check if a file exists but I'm assuming it'll fail if that directory is currently being created, right?
How do I assure thread safety?
I willing to change my idea and not use rand(). Is there a way to get a unique key for each anonymous user on my site?
The Code Below is for my Colony Building page for my game. That particular code is supposed to display the number of buildings built, which it does, and also allow me to change the number of buildings built, which it doesn't. The code is part of a loop that displays all the possible buildings of a given type, in this case Iron Mines, that can be built on the colony. Basically what I need to know is, how do I change the name of the column that is being updated as the code loops. Currently it is setting all the forms to alter the number of Basic iron mines on a colony. I am hoping I can somehow call the names of all the columns into an array and use the same code that picks which column value i am selecting, the $?(an array)[$b](an incrementing number to call the correct array value): $?[$b]. Then I would replace Basic, in the column part of the UPDATE code with the array and result selector so that the first time i include() the code it creates the form and sets it to alter Basic, but the second time it sets the code to alter the value in the next column. Code: [Select] <?php $dbhost = 'localhost:3306'; $dbuser = 'root'; $dbpass = 'root'; $dbname = 'aosdb'; $conn = mysql_connect($dbhost,$dbuser,$dbpass) or die ('Error connecting to mysql'); mysql_select_db($dbname); $b=1; $queryp = "SELECT * FROM IMBuilt WHERE idcol = $idcol"; $resultp = mysql_query($queryp); $Base = mysql_fetch_row($resultp); if(isset($_POST["built"])) { if($_POST) { $Badd = $Base[$b]+$_POST["built"]; $queryi="UPDATE IMBuilt SET Basic=$Badd WHERE idcol = $idcol"; mysql_query($queryi); } } $queryf = "SELECT * FROM IMBuilt WHERE idcol = $idcol"; $resultf = mysql_query($queryf); $Basic = mysql_fetch_row($resultf); ?> <p class="bd" id="built">| Built: <?php echo $Basic[$b] ?> |</p> <form class="bd" method="post" action="IronMineList.php"><input type="varchar" name="built"><input type="submit" value="Build"></form> </div> Hello, I'm having some issues with PHP thinking that the variables that I send it are the actual columns in my database. First, I pull off Quadratic_Functions and introductory_problem from http://localhost:8888/algebra_book/Chapters/Quadratic_Functions/introductory_problem.php using the code below: Code: [Select] $chapter_page = $_SERVER['PHP_SELF']; $chapter_page_array = explode('/',$chapter_page); $size =count($chapter_page_array); $chapter = $chapter_page_array[$size-2]; $page = $chapter_page_array[$size-1]; $page_array = explode('.', $page); $page = $page_array[0]; Based on my printing of the variables $chapter and $page I think that it's doing what I want it to do. I then use the following function: Code: [Select] $supplemental_id = getSupplementalId($dbRead,$chapter,$page); to check out if the there's a supplemental_id for the Quadratic_Function chapter and introductory_problem page name via: Code: [Select] function getSupplementalId($read,$user_id,$chapter,$page) { $sql = "SELECT supplemental_id FROM instructors_supplemental WHERE page_name = $page AND chapter_name='$chapter"; return $read->fetchRow($sql); } If I stick in actual values, as seen below, the thing runs fine. Code: [Select] $sql = "SELECT supplemental_id FROM instructors_supplemental WHERE page_name = 'introductory_problem' AND chapter_name='Quadratic_Functions'"; But if I run it in the abstract version, with variables for page and chapter name (the first version), I get Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'introductory_problem' in 'where clause'' in... It's almost as if it thinks that my variables are the names of the columns. Any thoughts would be appreciated.... This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=330627.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=344739.0 I'm pulling data from an xml file and I'm getting a lot of duplicates. How can I remove the duplicates from the query? Code: [Select] foreach($xml->category->subcategory as $category){ echo $category[id]; }//end foreach I'm using an INNER JOIN to grab some data from 2 tables depending on if another's id matches an id within the other table (obviously.. ). Is there anyway to continue on with this join but to filter even deeper by only taking data that not only has matching some_id but also does not have a row that equals a certain value? Something along the lines of adding "WHERE some_row != 'value'" ? "SELECT * FROM my_firstTable INNER JOIN my_secondTable ON my_firstTable.some_id = my_secondTable.some_id ORDER BY my_firstTable.some_id" I am trying to write a script that runs through a CSV file and inserts unique values in a mysql database and spits the duplicate out into a new CSV. I can find the duplicates when I load it into an array, but when I insert them into a MySQL database, I cannot determine if they are duplicates. If I use INSERT IGNORE, it will run the whole file, and reject the duplicates. This is fine, except I need to create a file of the duplicate entries. What is the best way to determine if the insert attempt was rejected? I could do a select to see if the row is there, spit it out...insert if not, but I was thinking there should be a way for MySQL to talk back to me to cut the queries in half... Any ideas? Thanks Okay, so I'm trying to run a query to pull the information from a specific item that they click on in order to edit it. When I run an echo $query, though, it shows the field names rather than the information from the table. How can I make it so that it pulls the information rather than the field names? Here's what I have... <?php include("lib.php"); define("PAGENAME", "Edit Equipment"); if ($player->access < 100) $msg1 = "<font color=\"red\">"; //name error? $error = 0; $query = $db->execute("SELECT * FROM items WHERE id=?", array($_POST['id'])); echo "$id"; $result = mysql_query($query); echo "$query"; $data = mysql_fetch_assoc($result); $msg1 .= "</font>"; //name error? ?> Thanks!! I am trying to write some code to display the visits to my site each week in batches of 13 weeks (1 quarter).
I am getting the Week No and the number of hits, but the actual date, which is in a separate table is not being displayed.
I have tried LEFT JOIN and INNER Join and even RIGHT JOIN (this only displays the Date).
I can't see where I am going wrong!
Here is the code:
<? include('connect_visits.php'); doDB7(); $limitStart = $_POST['QUARTER'] - 13; $Visit_data="SELECT Visits.ID, Visits.WVisits, Visits.WCom, WeekNo.WNo, WeekNo.WCom FROM Visits LEFT JOIN WeekNo ON Visits.ID = WeekNo.WCom ORDER BY Visits.ID LIMIT {$limitStart}, 13"; $Visit_data_res = mysqli_query($mysqli, $Visit_data) or die(mysqli_error($mysqli)); $display_block = " <table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" > <tr> <th>Week No</th> <th>Week Commencing</th> <th>Visits</th> </tr>"; while ($C_info = mysqli_fetch_array($Visit_data_res)) { $Cid = $C_info['ID']; $Visits = ($C_info['WVisits']); $WeekNo = ($C_info['WCom']); //add to display $display_block .= " <tr> <td width=\"1%\" valign=\"top\">".$Cid."<br/></td> <td width=\"8%\" valign=\"top\">".$WeekNo."<br/></td> <td width=\"5%\" valign=\"top\">".$Visits."<br/></td> </td></tr>"; } mysqli_free_result($Visit_data_res); ?>Where am I going wrong? Edited by rocky48, 29 December 2014 - 12:53 PM. I have two tables 'book' and 'category'. They look like the following in phpmyadmin;
book
id title author category isbn ---- ------- ---------- ---------- ------- 1 Treasure Chest Jim Jones 1 14252637 2 Pirates Boat Sue Smith 2 88447737 3 Adventure Land Harry Jo 3 01918273 4 Winter Week Sam Dill 3 00999337 5 The Twite Roald Dahl Fiction 87873366 category id cat_name ---- ------- 1 Horror 2 Kids 3 Fiction 4 Science Users have the option of adding books into the library via an online form, or via a Google Booka api method (user enters isbn, searches, is presented with book info and then clicks 'add to library', done.). This is handled via ajax. The online form works fine, and successfully adds the book info. However my problem is with the Google Books method, it successfully adds the data to the db however the category column is in text format (i.e 'Juvenile Science' or 'Scary Fiction') as opposed to the manual form which adds categories as 1, 2 or 3 (1 =Horror, 2 = Kids, 3 = Fiction). Is there any way I can add the Google Book category data to my table and convert it to an integer or similar? Not sure what I need to do. Suggestions appreciated! Should I add the Google entries to another table (i.e googleCategory)? My HTML only outputs the numbered category entries and ignored the text format entries. my php $sql = "SELECT b.id, b.title, b.author, b.isbn, b.publicationYear, c.cat_name FROM book AS b INNER JOIN category AS c ON b.category = c.id WHERE status != 'Archive' ORDER BY id DESC LIMIT $startrow, 15 "; $res = $conn->query($sql) or trigger_error($conn->error."[$sql]"); while($row = $res->fetch_array()) { echo '<tbody>'; echo '<tr>'; echo '<td>' . $row['id'] . '</td>'; echo '<td>' . $row['title'] . '</td>'; echo '<td>' . $row['author'] . '</td>'; echo '<td>' . $row['cat_name'] . '</td>'; echo '<td>' . $row['isbn'] . '</td>'; echo '<td>' . $row['publicationYear'] . '</td>'; echo '</tr>'; echo '</tbody>'; };Apologies if this is all a bit confusing I am very new to php and mysql. Thanks, J I have a mysql table which will store users email addresses (each is unique and is the primary field) and a timestamp. I have added another column called `'unique_code' (varchar(64), utf8_unicode_ci)`. What I would very much appreciate assistance with is; a) Generating a 5 digit alphanumeric code, ie: 5ABH6 b) Check all rows the 'unique_code' column to ensure it is unique, otherwise re-generate and check again c) Insert the uniquely generated 5 digit alphanumeric code into `'unique_code'` column, corresponding to the email address just entered. d) display the code on screen. What code must I put and where? **My current php is as follows:** Code: [Select] require "includes/connect.php"; $msg = ''; if($_POST['email']){ // Requested with AJAX: $ajax = ($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest'); try{ if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){ throw new Exception('Invalid Email!'); } $mysqli->query("INSERT INTO coming_soon_emails SET email='".$mysqli->real_escape_string($_POST['email'])."'"); if($mysqli->affected_rows != 1){ throw new Exception('You are already on the notification list.'); } if($ajax){ die('{"status":1}'); } $msg = "Thank you!"; } catch (Exception $e){ if($ajax){ die(json_encode(array('error'=>$e->getMessage()))); } $msg = $e->getMessage(); } } How can i get the last row data from a certain column? This 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'm doing my best to research this stuff guys, but I keep getting stuck. My reporting is almost finished, but I'm stuck at the part of my code that's denoted by "//FOR EACH LOOP HERE?" Can someone help? in addition to this missing code I need, I also get the error "mysqli_num_rows() expects parameter 1 to be mysqli_resul". But all these examples on the web use pretty much the same code like this. <?php $dbHost = "localhost"; $dbName= "rptDatabase"; $dbUsername = "username"; $dbPassword = "password"; $conn = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName); if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $sql = mysqli_query($conn, "SELECT * FROM tblTraffic ORDER BY ip, date, time"); $report = mysqli_fetch_array($sql); echo "<table border='1'> <tr> <th>IP ADDRESS</th> <th>PAGE VISITED</th> <th>PREVIOUS PAGE</th> <th>DATE</th> <th>TIME</th> </tr>"; if (mysqli_num_rows($report) > 0) { while($row = mysqli_fetch_array($report)) { echo "<tr>"; //FOR EACH LOOP HERE? } } else { echo "0 results"; } $conn->close(); ?> Edited October 14, 2019 by ajetrumpet <html> <title>Micro Elite Brigade - Participated Events</title><LINK REL="SHORTCUT ICON" HREF="images/favicon.png"><?php require_once('upper.php'); if($_COOKIE['LoginIdCookie']){ require_once('database.php'); require_once('LoginStatement.php'); $LoginId=$_COOKIE['LoginIdCookie']; $query="select * from participation where LoginId='$LoginId'"; $result=mysqli_query($dbc,$query) or die ('Not Connected'); //echo "<div class='search_output_data'> echo "<table border='0' cellspacing='0' cellpadding='0'>"; echo "<tr><th align='left' valign='top' width='100' height='20'>Event Title</th> <th align='right' valign='top' width='250' height='30'>Date of Participation</th> </tr>"; while($row=mysqli_fetch_array($result)) { echo " <tr><td colspan='4' align='left' valign='top' ><table align='left' border='0' cellspacing='0' cellpadding='0'><tr><td align='left' valign='top' width='200' height='20'>".$row['Title']."</td> <td width='50'> </td> <td align='left' valign='top' width='130'>".$row['Date']."</td> </tr></table> </td></tr>"; } echo "</table>";} else{ echo 'Sorry, You must have to login.';} require_once('lower.php'); ?> HI friends..... I have a table named events in which I have a "Title" column. When I print the value of Title column, it not displayed not more than 40 characters.... Where is the problem ??? Anyone?????? I have some php code on view invoice and it's getting all the data I need to from three database tables but for some reason it's not getting the data for one specific column and unsure why I have done a echo sql and it is echoing the sql query ok and have ran the sql query in phpmyadmin and is displaying the column in phpmyadmin that is not displaying on the php page so am bit confused, below is the coding I have. I took out a lot of it and tried to keep to the relevant parts <?php $sql = "SELECT t1.id, t1.invoice_number, DATE_FORMAT(t1.invoice_date,'%d/%m/%Y') AS invoice_date, t1.user_id, t2.invoice_number, t2.service_name, t2.service_price, t3.user_id, t3.customer_name, t3.customer_phone, t3.customer_email, t2.service_name, t2.service_price, t1.invoice_total, t1.balance, t1.notes, DATE_FORMAT(t1.payment_date,'%d/%m/%Y') AS payment_date, t1.payment_method FROM invoices as t1 LEFT JOIN invoice_products as t2 ON t1.id = t2.invoice_id LEFT JOIN users as t3 ON t1.user_id = t3.user_id WHERE t1.user_id = '".$_SESSION['user_id']."' and t1.id = '".$_GET['id']."'"; $result = mysqli_query($connect, $sql); if(mysqli_num_rows($result) > 0) { if($row = mysqli_fetch_array($result)) { ?> Notes: <?php echo $row['notes'];?> <?php } } ?>
Hi, I have this script that returns the entire table including column names and the row data. However I only want certain column names and the corresponding data to be returned. Code: [Select] <?php include_once("data/mysql.php"); $mysqlPassword = (base64_decode($mysqlpword)); $db = mysql_connect("$localhost", "$mysqlusername", "$mysqlPassword") or die ("Error connecting to database"); mysql_select_db("$dbname", $db) or die ("An error occured when connecting to database"); // sending query $result = mysql_query("SELECT * FROM members"); if (!$result) { die("Query to show fields from table failed"); } $fields_num = mysql_num_fields($result); echo "<h1>Table: members</h1>"; echo "<table border='1'><tr>"; for($i=0; $i<$fields_num; $i++) { $field = mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; while($row = mysql_fetch_row($result)) { echo "<tr>"; foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_query($result); ?> Any help is always appreciated This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=308855.0 |