Join db Tables in CRUD application (in update)

I’m in the process of creating a CRUD application and the update and the links are populating well on the webpage.

What I’m trying to do is populate links that only pertain to the user that is logged in, and I’m not sure how to do this properly.

Here is a link to the website:

http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/index.php user: Test1 password: test1234 to get to the update page you’ll need to click update reports.

this is my update page:

<?php include("includes/session.php"); ?>

<?php include("includes/reports_mysql_connect.php"); ?>
<?php

	
	$id = $_GET['id'];

	if(!isset($id)){
		$x = mysql_query("SELECT id FROM Reports ORDER BY timedate DESC") or die(mysql_error());
		while($row = mysql_fetch_array($x)){
			$id = $row['id'];
		}
	}

	//step 3: user submits changes, updates DB to show new information
	if(isset($_POST['submit'])){

		$new_sale = $_POST['sale'];
		$new_questiontwo = $_POST['questiontwo'];
		$new_questionthree = $_POST['questionthree'];
		$new_questionfour = $_POST['questionfour'];
		$new_questionfive = $_POST['questionfive'];
		$new_questionsix = $_POST['questionsix'];
		$new_questionseven = $_POST['questionseven'];
		$new_questioneight = $_POST['questioneight'];

		echo "$new_sale | $new_questiontwo" ; 

		mysql_query("UPDATE Reports SET

			sale = '$new_sale',
			questiontwo = '$new_questiontwo',
			questionthree = '$new_questionthree',
			questionfour = '$new_questionfour',
			questionfive = '$new_questionfive',
			questionsix = '$new_questionsix',
			questionseven = '$new_questionseven',
			questioneight = '$new_questioneight'
			
			WHERE id = '$id' ") or die(mysql_error());
	}


	//step 1: Retrieve data to create links
	$result = mysql_query("SELECT * FROM Reports ") or die(mysql_error());

	while($row = mysql_fetch_array($result)){
		$sale = $row['sale'];
		$questiontwo = $row['questiontwo'];
		$questionthree = $row['questionthree'];
		$questionfour = $row['questionfour'];
		$questionfive = $row['questionfive'];
		$questionsix = $row['questionsix'];
		$questionseven = $row['questionseven'];
		$questioneight = $row['questioneight'];

		//$id = $row['id'];
		//echo $id;

		echo '<div>' . $row['id'] . '<a href="updated-reports.php?id=' . $row['id'] . '">' . $sale . '</a></div>';
	}

	//Step 2: Retrieve Data for selected report only to prepopulated form.

	$result = mysql_query("SELECT * FROM Reports WHERE name='$id'") or die(mysql_error());
	while($row = mysql_fetch_array($result)){
		
		$thissale = $row['sale'];
		$thisquestiontwo = $row['questiontwo'];
		$thisquestionthree = $row['questionthree'];
		$thisquestionfour = $row['questionfour'];
		$thisquestionfive = $row['questionfive'];
		$thisquestionsix = $row['questionsix'];
		$thisquestionseven = $row['questionseven'];
		$thisquestioneight = $row['questioneight'];

		//$thisid = $row['id'];
		//echo $id;
	}

?>



<?php include("../includes/admin-leads-header.php") ?>

<div class="container">
	<div class="sixteen columns">
	 	 <section class="fifteen columns">
	 	 	<div class="general">
	 	 		<div class="project-leads">

					<?php

						if($session->logged_in){
						if($session->isAdmin()){
						}
					
						echo "<h3>Hello $session->username</h3>";
						echo "<h5>Please enter your sales leads reports</h5>";
					?>

					<div class="project-leads-reports">
						<form name="contactform" id="contactform" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
							<ul>
								<li>
									<span class="required">*</span>
									<label for="name">From: </label>
									<input type="text" name="name" id="name" value="<?php echo $session->username?>" />
									<span class="error"></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="email">E-mail Address: </label>
									<input type="text" name="email" id="email" value="<?php if($form->value("email") == ""){
											echo $session->userinfo['email'];
										}else{
							   				echo $form->value("email");
										}?>" />
									<span class="error"></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="boss">Regional Sales Manager E-mail Address:</label>
									<input type="text" name="boss" id="boss" value="<?php if($form->value("boss") == ""){
											echo $session->userinfo['boss'];
										}else{
											echo $form->value("boss");
										}?>" />
									<span class="error"></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="Sales">Sales Lead Subject:</label>
									<input type="text" name="sale" id="sale" value="<?php echo trim($thissale);?>" />
									<span class="error"></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="questiontwo">When did you followup with the General Contractor?</label>
									<textarea name="questiontwo"  id="questiontwo" ><?php echo trim($thisquestiontwo);?></textarea>
									<span class="error"><?php echo $strValidationMessage2; ?></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="questionthree">Who is/are the contractors working under the General Contractor?</label>
									<textarea name="questionthree" id="questionthree"><?php echo trim($thisquestionthree);?></textarea>
									<span class="error"><?php echo $strValidationMessage3; ?></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="questionfour">Did the General Contractor have their own subcontractors, or did we refer our contractor customers to them?</label>
									<textarea name="questionfour"  id="questionfour"><?php echo trim($thisquestionfour);?></textarea>
									<span class="error"><?php echo $strValidationMessage4; ?></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="questionfive">Can we or did we submit a price?</label>
									<textarea name="questionfive" id="questionfive" ><?php echo trim($thisquestionfive);?></textarea>
									<span class="error"><?php echo $strValidationMessage5; ?></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="questionsix">Did we win and why did we win or did we lose or why did we lose?</label>
									<textarea name="questionsix" id="questionsix" ><?php echo trim($thisquestionsix);?></textarea>
									<span class="error"><?php echo $strValidationMessage6; ?></span>
								</li>
								<li>
									<span class="required">*</span>
									<label for="questionseven">What is being done to progress any sales forward with this Project Lead?</label>
									<textarea name="questionseven" id="questionseven" ><?php echo trim($thisquestionseven);?></textarea>
									<span class="error"><?php echo $strValidationMessage7; ?></span>
								</li>
								<li>
									<span class="required"></span>
									<label for="questioneight">Did you know about this project before it was assigned and or any additional comments?</label>
									<textarea name="questioneight"  id="questioneight"><?php echo trim($thisquestioneight);?></textarea>
									<span class="error"></span>
								</li>
								<li>
									<input type="submit" name="submit" id="submit" value="Submit" />
								</li>
							</ul>
						</form>
					</div><!--end of project lead reports -->	

					
				<?php }?>

				
				
				</div><!--end of project-leads-->
		    </div><!--end of general-->
        </section>
    </div> 
</div>


<?php include("../includes/admin_footer.php") ?>

Here are the DB tables I’m trying to join

http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/users.png
– Table structure for table blog_users

CREATE TABLE blog_users (
boss varchar(50) NOT NULL,
zoneregion varchar(50) NOT NULL,
username varchar(30) NOT NULL,
password varchar(32) default NULL,
userid varchar(32) default NULL,
userlevel tinyint(1) unsigned NOT NULL,
email varchar(50) default NULL,
timestamp int(11) unsigned NOT NULL,
PRIMARY KEY (username)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

http://consolidatedgypsum.ca/cgs-new-site/admin/project-leads/Reports.png
CREATE TABLE Reports (
timedate timestamp NOT NULL default CURRENT_TIMESTAMP,
name varchar(200) character set latin1 NOT NULL,
email varchar(200) character set latin1 NOT NULL,
sale text character set latin1 NOT NULL,
questiontwo text character set latin1 NOT NULL,
questionthree text character set latin1 NOT NULL,
questionfour text character set latin1 NOT NULL,
questionfive text character set latin1 NOT NULL,
questionsix text character set latin1 NOT NULL,
questionseven text character set latin1 NOT NULL,
questioneight text character set latin1 NOT NULL,
boss text character set latin1 NOT NULL,
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=106 ;

Thanks in advance

Hi lauren_olsen17, welcome to the forum.

What Course/Book are you following? It should be a help to compare with the Learnable source code.

I’m not I’m try to go off my previous Notes from NAIT, and off this site with the Understanding JOINs. I tried to upload the images of both my table structures, that I’m trying to use, but being a new user, it wouldn’t allow for it

OK, I see, you gave the topic a Learnable tag in error. (I’ve since removed it).

You should be a Basic user and be able to upload images soon.

I see the code is old and could really benefit from changing from using deprecated mysl_ functions to the mysqli_ functions or PDO before it breaks and you are forced to.

I see only one table in the queries i.e. Reports

The queries are

SELECT id FROM Reports ORDER BY timedate DESC
UPDATE Reports SET
			sale = '$new_sale',
			questiontwo = '$new_questiontwo',
			questionthree = '$new_questionthree',
			questionfour = '$new_questionfour',
			questionfive = '$new_questionfive',
			questionsix = '$new_questionsix',
			questionseven = '$new_questionseven',
			questioneight = '$new_questioneight'
			WHERE id = '$id' 
SELECT * FROM Reports 
SELECT * FROM Reports WHERE name='$id'

The query responsible for generating the links is
SELECT * FROM Reports

See anything that stands out as the possible problem with it?

What’s more concerning is the fact that user submitted data is being allowed near the database without having been sanitized and escaped. Once you’ve migrated over to either the mysqli_* extension or PDO you should be using prepared statements to prevent SQL injection attacks

I wasn’t aware that mysql_ was depreciated code, this what they taught me in school, as I just graduated at the end of April 2014. If it helps, I’m not extremely fluent in PHP, my niche is CSS and JS.

I do have real escape strings to prevent harmful injections, there included in this include
"<?php include("includes/reports_mysql_connect.php"); ?>

Also I only have the one query in there, because I’m not sure how to add in the second query as a JOIN in an update function of CRUD.

If this is primarily a query problem as opposed to PHP code, you could start a topic in Databases.
Please be sure to show your table schemas.

BTW, if you missed the event, you are now a “Basic” member and can upload images :smile:

Wouldn’t it be a query question, if I’m not sure how to join the tables properly in a update ??

There is often “overlap” where a topic “best fits”, but yes, if it’s a JOIN query question Databases is the category you are more likely to get the better/quicker response.

Is there a way to add a tag for Database when a question has already been submitted, rather than writing it agan? Also with mySql being depreciated, is it easy enough to switch over mySqli without having to change a lot of my current php code?

There are no Tags for subjects that are Categories.
Topic Moved.

Thanks, it’s been moved to databases?

okay, i read the entire thread but skipped over all the php stuff

what is your database question?

I know it’s not in the code, but what would be the best way to JOIN the tables above in CRUD (update) application. I’m not sure how to go about this properly.

What I’m trying to accomplish is: based on what user is logged on, only show the their reports/submissions to be updated, not everyone’s. Currently as of now, it’s everyone’s showing reports/submissions.

I understand that mySql_functions are depreciated and I’m in the process of changing them over to mySqli_functions.

it looks like there’s four tables, but i only see two

please, make it a bit easier for us, for each table, please run a SHOW CREATE TABLE query in the SQL window (right beside your Structure window)

example –

SHOW CREATE TABLE reports

and then copy/paste the text result here (it’s a lot easier working with text than images)

added, is this everything you were looking for?

Also how depreciated is mysql? will hurt if I continue to use it?

[quote=“lauren_olsen17, post:17, topic:105517, full:true”]
added, is this everything you were looking for?
[/quote]i need some more info

how do you know which user a specific row in the reports table belongs to?

also, why do you run the first query to get the id of all reports?

also, CRUD means “create, reference, update, delete” but your UPDATE statement only covers the “U” part of CRUD… are the other parts under control?

I know what CRUD means, I haven’t included the CREATE, Reference or Delete, do you need to see those? In the Reports table by name or email, will specify who it’s for. In this update, this is how I was taught to run a update, so if there is a better please let me know. That would be the reason the my first query is as is.