I can query one table, not another, yet they are on the same DB.

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 1185 Views - Last Post: 13 February 2012 - 01:43 PM Rate Topic: -----

#1 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

I can query one table, not another, yet they are on the same DB.

Posted 03 February 2012 - 12:48 PM

Ok, I think I may of solved my other issue, but doing so has caused me to find an error, and I can't seem to find an answer of why it's not working.

The code should show:

  • Query is : SELECT * FROM testbed WHERE id = '1'
  • Query is : SELECT user FROM user WHERE id = 'Andy Khal'


It doesn't, I'd like to know why.

The page is here: http://kaboomlabs.co...ed/box.php?id=1

Here is the code in question:
<?php
require_once('tb/connectvars.php');
	$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$id= mysqli_real_escape_string($dbc, trim($_GET["id"]));
$id2= mysqli_real_escape_string($dbc, trim($_GET["userid"]));

$query= "SELECT * FROM testbed WHERE id = '$id'";
$query2= "SELECT user FROM user WHERE id = '$id2'";

// echo your raw query and look for obvious errors
echo "Query is : " . $query . "<br />";
echo "Query is : " . $query2 . "<br />";


print $row['fab1'];
?>



Is This A Good Question/Topic? 0
  • +

Replies To: I can query one table, not another, yet they are on the same DB.

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,320
  • Joined: 08-August 08

Re: I can query one table, not another, yet they are on the same DB.

Posted 03 February 2012 - 01:10 PM

Look at the link you provided. "id" is set, but "userid" is not.

Is `id` in `user` not an integer?

Try this:
http://kaboomlabs.co...rid=Andy%20Kahl
Was This Post Helpful? 1
  • +
  • -

#3 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: I can query one table, not another, yet they are on the same DB.

Posted 03 February 2012 - 01:14 PM

View PostCTphpnwb, on 03 February 2012 - 01:10 PM, said:

Look at the link you provided. "id" is set, but "userid" is not.

Is `id` in `user` not an integer?

Try this:
http://kaboomlabs.co...rid=Andy%20Kahl


Ok, just tried what you suggested...no go.


<?php
require_once('tb/connectvars.php');
	$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$id= mysqli_real_escape_string($dbc, trim($_GET["id"]));
$id2= mysqli_real_escape_string($dbc, trim($_GET["userid"]));

$query= "SELECT * FROM testbed WHERE id = '$id'";
$query2= "SELECT user FROM user WHERE userid = '$id2'";

// echo your raw query and look for obvious errors
echo "Query is : " . $query . "<br />";
echo "Query is : " . $query2 . "<br />";


print $row['fab1'];
?>


This post has been edited by MattRidge: 03 February 2012 - 01:16 PM

Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,320
  • Joined: 08-August 08

Re: I can query one table, not another, yet they are on the same DB.

Posted 03 February 2012 - 01:30 PM

It worked for me, but maybe you're referring to the query actually failing. If that's the case then as I mentioned above, I think that your user table probably has `id` set as an integer, so using a name isn't going to work.
Was This Post Helpful? 0
  • +
  • -

#5 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: I can query one table, not another, yet they are on the same DB.

Posted 03 February 2012 - 01:43 PM

View PostCTphpnwb, on 03 February 2012 - 01:30 PM, said:

It worked for me, but maybe you're referring to the query actually failing. If that's the case then as I mentioned above, I think that your user table probably has `id` set as an integer, so using a name isn't going to work.


I figured out the issue, I just need to figure out how to code to make it work.

Basically id=1 is accurate for testbed, but not user, what I need to do is make it so that it references a field in testbed then to show the result in user of said number in field in testbed.

So in English:

Testbed has a field named number, it has a number in it of 2. From there I need to reference that by looking into the user table, looking up userid, and then when it finds the number 2 it prints out the user field, which would be Andy Khal.

Does this make sense?
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: I can query one table, not another, yet they are on the same DB.

Posted 03 February 2012 - 09:19 PM

If I am understanding you correctly, you are talking about selecting a row from testbed, matching a value from that row to a user ID, and then showing that user?

That would be done using a JOIN. They combine tables in to a single result set, which you can then filter however you like.

SELECT * FROM user
JOIN testbed
	ON testbed.number = user.id
WHERE testbed.id = 1


The first line just initializes the result set as the entire user table. The second line imports the testbed table into that result set, and the third line tells the database how to do that import. - If that last part is unclear, think of it this way: For every row in the user table, the database will execute a SELECT * FROM testbed query, using the ON conditions as a WHERE clause. And if that SELECT returns anything, it will merge each row from the SELECT with the current row from the user table and add that to the final result set.

Then the final WHERE clause kicks in to filter the final result set, leaving you with only the row from testbed where the id is 1, which will no include the user information that matches it. - Note that if more than one user row matches that one testbed row, you'll get more than one row back; one for each user.
Was This Post Helpful? 1
  • +
  • -

#7 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 06:14 AM

View PostAtli, on 03 February 2012 - 09:19 PM, said:

If I am understanding you correctly, you are talking about selecting a row from testbed, matching a value from that row to a user ID, and then showing that user?

That would be done using a JOIN. They combine tables in to a single result set, which you can then filter however you like.

SELECT * FROM user
JOIN testbed
	ON testbed.number = user.id
WHERE testbed.id = 1


The first line just initializes the result set as the entire user table. The second line imports the testbed table into that result set, and the third line tells the database how to do that import. - If that last part is unclear, think of it this way: For every row in the user table, the database will execute a SELECT * FROM testbed query, using the ON conditions as a WHERE clause. And if that SELECT returns anything, it will merge each row from the SELECT with the current row from the user table and add that to the final result set.

Then the final WHERE clause kicks in to filter the final result set, leaving you with only the row from testbed where the id is 1, which will no include the user information that matches it. - Note that if more than one user row matches that one testbed row, you'll get more than one row back; one for each user.


Now this will be getting info from a link,which will then open to this page, which will run this script, so in this case the link will be this:

http://kaboomlabs.co...ed/box.php?id=1

So if I enter the code:

if(isset($_GET['id']))
    $id= mysqli_real_escape_string($dbc, trim($_GET["id"]));




Will this work?:

SELECT * FROM user
JOIN testbed
	ON testbed.fab1= user.userid
WHERE testbed.id = "$id"


Was This Post Helpful? 0
  • +
  • -

#8 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6063
  • View blog
  • Posts: 23,515
  • Joined: 23-August 08

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 07:20 AM

Instead of asking "will this work", why not...oh I don't know...TRY it? What's the point of asking such a question when it's so easily testable?
Was This Post Helpful? 2
  • +
  • -

#9 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 07:22 AM

View PostJackOfAllTrades, on 06 February 2012 - 07:20 AM, said:

Instead of asking "will this work", why not...oh I don't know...TRY it? What's the point of asking such a question when it's so easily testable?


I'm asking because I don't want to guess and try without knowing why it works. That isn't the correct way to write code.
Was This Post Helpful? -1
  • +
  • -

#10 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,320
  • Joined: 08-August 08

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 07:34 AM

All code must be tested, and in testing it you learn something. You either learn that it works or that it doesn't, and if it doesn't you should learn why it doesn't. That is the correct way to write code.
Was This Post Helpful? 1
  • +
  • -

#11 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 07:47 AM

View PostCTphpnwb, on 06 February 2012 - 07:34 AM, said:

All code must be tested, and in testing it you learn something. You either learn that it works or that it doesn't, and if it doesn't you should learn why it doesn't. That is the correct way to write code.


I agree, but when I don't even understand how this is going to work, or where to put this inside the script I provided originally, I have to ask because adding this scrip in there pell-mell will not help me make it work, especially with code that looks like this right now:

<?php
require_once('tb/connectvars.php');
	$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if (isset($_POST['submit'])) {
	$fab1= 'fab1';
$query = "UPDATE testbed SET fab1 = '" . mysqli_real_escape_string($dbc, $fab1) . "' WHERE id = '$id'";
}
			if(isset($_GET['id'])){
		$id= mysqli_real_escape_string($dbc, trim($_GET["id"]));
		$fab1= mysqli_real_escape_string($dbc, trim($_GET["userid"]));
		

$query = "SELECT * FROM testebed WHERE id = '$id'";
$query2 = "SELECT * FROM user WHERE userid = '$id2'";

// echo your raw query and look for obvious errors
	echo "testbed output is: " . $query . "<br />";
	echo "user output is: " . $query . "<br />";


echo '<h2>Fabricators</h2>';
echo "<form action='".$_SERVER['PHP_SELF']."' method='post'>";
	echo '<div id="box4">';
		echo '<div id="fab1">';
			$result = $fab1->query("SELECT * FROM user"); 
		echo "<select name='fab1'>\n";
			while($row = $result->fetch_assoc())
{
		echo '<option value="' . $row['fab1'] . '"';
			//compare the id in the current record to the one from the drop down 
			if($row['id'] == $fab1){
				echo ' selected';
				}
		echo '>' . $row['user'] . '</option>\n';
}
echo '<div id="button3"><input type="submit" value="Submit Test" name="submit" /></div>';
}
		echo '</div>';
	echo '</div>';
echo '</form>';
?>


This post has been edited by MattRidge: 06 February 2012 - 07:53 AM

Was This Post Helpful? 0
  • +
  • -

#12 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,320
  • Joined: 08-August 08

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 08:28 AM

Start by consistently using an indent style. That will help to make your code more readable. Functions can also help with readability.
Was This Post Helpful? 0
  • +
  • -

#13 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 08:42 AM

View PostJackOfAllTrades, on 06 February 2012 - 07:20 AM, said:

Instead of asking "will this work", why not...oh I don't know...TRY it? What's the point of asking such a question when it's so easily testable?


I've tried it...

<?php
require_once('tb/connectvars.php');
	$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$id= mysqli_real_escape_string($dbc, trim($_GET["id"]));
$id2= mysqli_real_escape_string($dbc, trim($_GET["userid"]));
$query= "SELECT * FROM testbed WHERE id = '$id'";
$query2= "SELECT * FROM user JOIN testbed ON testbed.fab1 = user.userid WHERE testbed.fab1 = '$id2'";


// echo your raw query and look for obvious errors
echo "Query is : " . $query . "<br />";
echo "Query is : " . $query2 . "<br />";


  echo '' . $row['fab1'] . '';
  ?>



It doesn't work and I don't know why. Can you help please?

View PostCTphpnwb, on 06 February 2012 - 08:28 AM, said:

Start by consistently using an indent style. That will help to make your code more readable. Functions can also help with readability.


I use functions in my larger scripts, I don't use them in test scripts because I want to make sure they work at all in the first place.

As for tabbing, I agree using it helps, but again this is a simple script, I've grouped a lot of it together already, I figured the readability would be easy compared to a larger script.

As for the script you quoted on, it is using tabs, so I am somewhat wondering what you are looking at...

This post has been edited by MattRidge: 06 February 2012 - 08:53 AM

Was This Post Helpful? 0
  • +
  • -

#14 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,320
  • Joined: 08-August 08

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 09:14 AM

View PostMattRidge, on 06 February 2012 - 11:42 AM, said:

I use functions in my larger scripts, I don't use them in test scripts because I want to make sure they work at all in the first place.

Functions break up the code into small chunks that are each easily debugged. Whenever you don't use them your code is longer and harder to read/debug.

View PostMattRidge, on 06 February 2012 - 11:42 AM, said:

As for tabbing, I agree using it helps, but again this is a simple script, I've grouped a lot of it together already, I figured the readability would be easy compared to a larger script.

Indenting is more than just using tabs. It's using them consistently in a way that helps the reader understand the code without reading every single line.
Was This Post Helpful? 0
  • +
  • -

#15 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6063
  • View blog
  • Posts: 23,515
  • Joined: 23-August 08

Re: I can query one table, not another, yet they are on the same DB.

Posted 06 February 2012 - 09:21 AM

"It doesn't work...that's not good enough"

Could it "not work" because...you're not actually RUNNING the QUERY?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2