Jump to content

Select clause problem


kurt.santo
 Share

Recommended Posts

The region gets passed through the url and I store the result as:if (isset($_GET['region'])){$region = (int)$_GET['region'];Now I want to get the region name from database and I query as:$regionname = "SELECT region_name FROM region WHERE region_id = $region";Using var_dump I get "string(50) "SELECT region_name FROM region WHERE region_id = 8" S" instead of the name of the county. Where am I going wrong?Also what is best way to echo $regionname? I got lots of examples how to display array data, but cannot see how you would do it for a simgle result...Kurt

Link to comment
Share on other sites

You need to use mysql_fetch_assoc() or similar to retrieve the values from a resultset gained from mysql_query(). E.g.

$regionname = implode(mysql_fetch_assoc(mysql_query("SELECT region_name FROM region WHERE region_id = $region")));

At the moment all your code is doing is storing the query string to $regionname. Take another look...

Link to comment
Share on other sites

You need to use mysql_fetch_assoc() or similar to retrieve the values from a resultset gained from mysql_query(). E.g.
$regionname = implode(mysql_fetch_assoc(mysqli_query("SELECT region_id,region_name FROM region WHERE region_id = $region")));$result = mysqli_query ($dbc, $regionname);

I get the error message "mysqli_query() expects at least 2 parameters, 1 given". Why would the first line need another parameter and which would that be?Kurt

Link to comment
Share on other sites

I have read through there. The problem is that $regionname is NULL (did a dump):$regionname = implode(mysql_fetch_assoc(mysqli_query("SELECT region_id,region_name FROM region WHERE region_id = $region")));The select clause should be fine as I used it before ($counties = "SELECT county_id,county_name FROM county WHERE region_id = 2":), I also used successfully a variable as this ($result = mysqli_query ($dbc, $counties):). As syntax is not different apart from the "WHERE region_id = $region" bit, it does not make sense to me... Do you have any ideas?Kurt
Link to comment
Share on other sites

It's not the select statement, mysqli_query needs two arguments (look at the parameter list on the reference), you're only giving it one argument (the query).
I did try this before with same result. When I change the line to read:$regionname = implode(mysql_fetch_assoc(mysqli_query("SELECT region_id,region_name FROM region WHERE region_id = $region", MYSQLI_STORE_RESULT)));I get the error message "mysqli_query() expects parameter 1 to be mysqli, string given". I am after a string, so I do not know what goes wrong... Any ideas?Kurt
Link to comment
Share on other sites

http://www.php.net/manual/en/function.mysqli-query.phpmixed mysqli_query ( mysqli $link , string $query [, int $resultmode ] )the query needs to be in the 2nd parameter, in the first parameter u need the variable to wich u asigned the mysqli_connect to
I changed it to:$regionname = implode(mysql_fetch_assoc(mysqli_query($dbc, "SELECT region_id,region_name FROM region WHERE region_id = $region", MYSQLI_STORE_RESULT))); and still not working (tried it also without the MYSQLI_STORE_RESULT). It complains about that line and says "mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource". mysql_connect.php is:
if ($dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD)) { // make  connnection	if (!mysql_select_db (DB_NAME)) { // if it can't select the database		// handle the error		trigger_error("Could not select the database!\n<br />MySQL Error: " . mysql_error());		// print a message to the user and kill the script.		exit();	} // end of mysql_select_db IF} else { // if it couldn't connect to MySQL	// print a message to the user and kill the script	trigger_error("Could not connect to MySQL!\n<br />MySQL Error: " . mysql_error());	exit();}

I really do not understant. The counties get listed ok (other select clause). What is going wrong here? Sitting here for hours, cannot see the problem (not to say that I have much of a clue, just working my way with books)...Kurt

Link to comment
Share on other sites

Your mixing mysql and mysqli again. Why not just stick to mysql?
Synook,Thanks for your post. With regard to mysql and mysqli I see both sometimes mixed and do not know when you can use them together and when not. In my script for example: I changed the line now to read:$regionname = implode(mysqli_fetch_assoc(mysqli_query($dbc, "SELECT region_id,region_name FROM region WHERE region_id = $region", MYSQLI_STORE_RESULT))); and the error message is gone (so I assume the database bit works). And this also in my database connect file I use mysql. Why is that?Still: Instead of displaying the region it only displays the region_id. A dump shows "string(9) "8SCOTLAND"". Do you know why that is?Kurt
Link to comment
Share on other sites

With regard to mysql and mysqli I see both sometimes mixed and do not know when you can use them together and when not.

You cannot use mysql and mysqli together at all. Any connections made, queries executed, on one of them will not be accessable through the other family.

Instead of displaying the region it only displays the region_id. A dump shows "string(9) "8SCOTLAND"". Do you know why that is?

It is :) - SCOTLAND. Because we are imploding the result we get both in one string. We'll need to just get the array to distinguish between the two values

$region = mysqli_fetch_assoc(mysqli_query($dbc, "SELECT region_id,region_name FROM region WHERE region_id = $region", MYSQLI_STORE_RESULT));$region['region_id']; //8$region['region_name']; //SCOTLAND

Link to comment
Share on other sites

With regard to mysql and mysqli I see both sometimes mixed and do not know when you can use them together and when not.

You cannot use mysql and mysqli together at all. Any connections made, queries executed, on one of them will not be accessable through the other family.

Instead of displaying the region it only displays the region_id. A dump shows "string(9) "8SCOTLAND"". Do you know why that is?

It is :) - SCOTLAND. Because we are imploding the result we get both in one string. We'll need to just get the array to distinguish between the two values

$region = mysqli_fetch_assoc(mysqli_query($dbc, "SELECT region_id,region_name FROM region WHERE region_id = $region", MYSQLI_STORE_RESULT));$region['region_id']; //8$region['region_name']; //SCOTLAND

Finally, Scotland it is! I appreciate all your inputs and patience.Kurt
Link to comment
Share on other sites

Finally, Scotland it is! I appreciate all your inputs and patience.Kurt
Come back with one more thing: Wanted to prevent users going into address bar and typing a random number for region. So, after listing all my regions with an else if test clause I ended the whole block with
else {// other counties	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 11";	}

to display region 11 as a standard as such. When testing in browser and entereing a random number an error message comes saying "mysqli_query() [function.mysqli-query]: Empty query". This does not make sense as I thought the else clause means anything that was not tested before as such should be assigned to region 11. Is this not so?Kurt

Link to comment
Share on other sites

But where is the mysqli_query()? All I see is the query string being assigned to $countries, but the database isn't actually queried.

Link to comment
Share on other sites

But where is the mysqli_query()? All I see is the query string being assigned to $countries, but the database isn't actually queried.
Sorry, gave you just a fraction of my code. Here comes the whole code:
echo '<p>Please choose a country:</p>';require_once ('mysql_connect.php'); // connect to database// county displayif (isset($_GET['region'])){	$region = (int)$_GET['region'];// CHANNEL ISLANDS	if ($region == 1) {	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 1";		} else if ($region == 2) {// EAST ANGLIA	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 2";		} else if ($region == 3) {// EAST MIDLANDS	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 3";		} else if ($region == 4) {// LONDON	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 4";		} else if ($region == 5) {// NORTH EAST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 5";		} else if ($region == 6) {// NORTH WEST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 6";		} else if ($region == 7) {// NORTHERN IRELAND	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 7";		} else if ($region == 8) {// SCOTLAND	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 8";		} else if ($region == 9) {// SOUTH EAST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 9";		} else if ($region == 10) {// SOUTH WEST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 10";		} else if ($region == 11) {// WALES	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 11";		} else if ($region == 12) {// WEST MIDLANDS	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 12";		} else if ($region == 13) {// YORKSHIRE & HUMBERSIDE	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 13";	} else {// other counties	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 11";	}$regionname = mysqli_fetch_assoc(mysqli_query($dbc, "SELECT region_id,region_name FROM region WHERE region_id = $region", MYSQLI_STORE_RESULT));$regionname['region_id']; // id of region$regionname['region_name']; // name of region$result2 = mysqli_query ($dbc, $regionname['region_name']); echo $regionname['region_name'];$result = mysqli_query ($dbc, $counties);// display all counties in selected regionecho '<div id="listing">';while ($row = mysqli_fetch_array ($result, MYSQLI_ASSOC)){echo '<span><a href="listingTest.php?county=' . $row['county_id'] . '">' . $row['county_name'] . '</a></span>';} //end of while loopecho '</div>';echo '<p class="clearLeft">or select another region from the map</p><br />';}else{// a region was not selectedecho '<p>Please select a region</p><br />';}

Any ideas?Kurt

Link to comment
Share on other sites

$def_region = '1';if (isset($_GET['region'])){	$region = (int)$_GET['region'];// CHANNEL ISLANDS	if ($region == 1) {	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 1";		} elseif ($region == 2) {// EAST ANGLIA	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 2";		} elseif ($region == 3) {// EAST MIDLANDS	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 3";		} elseif ($region == 4) {// LONDON	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 4";		} elseif ($region == 5) {// NORTH EAST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 5";		} elseif ($region == 6) {// NORTH WEST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 6";		} elseif ($region == 7) {// NORTHERN IRELAND	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 7";		} elseif ($region == 8) {// SCOTLAND	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 8";		} elseif ($region == 9) {// SOUTH EAST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 9";		} elseif ($region == 10) {// SOUTH WEST	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 10";		} elseif ($region == 11) {// WALES	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 11";		} elseif ($region == 12) {// WEST MIDLANDS	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 12";		} elseif ($region == 13) {// YORKSHIRE & HUMBERSIDE	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 13";	} else {// other counties	$region = $def_region;	$counties = "SELECT county_id,county_name FROM county WHERE region_id = $region";	}

does the trick;-) Finally managed to figure out how to do it...Thanks for all inputs!Kurt

Link to comment
Share on other sites

Uh... couldn't your multiple assignments become

if (isset($_GET['region'])) $region = (int) $_GET['region'];else $region  = 1;if ($region > 13 || $region < 1) $region = 1;$counties = "SELECT county_id,county_name FROM county WHERE region_id = $region";

Link to comment
Share on other sites

Uh... couldn't your multiple assignments become
// CHANNEL ISLANDS	if ($region == 1) {	$counties = "SELECT county_id,county_name FROM county WHERE region_id = 1";

and leave the last bit out

else {// other counties	$region = $def_region;	$counties = "SELECT county_id,county_name FROM county WHERE region_id = $region";

I am really grateful to get advice in how to improve on my coding style etc...Kurt

Link to comment
Share on other sites

You can replace all of the code you posted in reply #17 with my one. And yes, yours is not so good because it is so long, and you repeat the query lots of times. What if your table name changed to e.g. england_counties?

Link to comment
Share on other sites

You can replace all of the code you posted in reply #17 with my one. And yes, yours is not so good because it is so long, and you repeat the query lots of times. What if your table name changed to e.g. england_counties?
Thanks for your info, but I just realised that this code puts a value in the querystring anyhow (1 if nothing was selected). For the first loading of the page there will be just a message saying "please select a county". How could I put this into your bit? I cannot assign 1 to $region right away. Could your code be adjusted to do that?Kurt
Link to comment
Share on other sites

if (isset($_GET['region'])) {$region = (int) $_GET['region'];if ($region > 13 || $region < 1) $region = 1;$counties = "SELECT county_id,county_name FROM county WHERE region_id = $region";//The rest of your code} else {echo "Please select a country";}

Link to comment
Share on other sites

if (isset($_GET['region'])) {$region = (int) $_GET['region'];if ($region > 13 || $region < 1) $region = 1;$counties = "SELECT county_id,county_name FROM county WHERE region_id = $region";//The rest of your code} else {echo "Please select a country";}

Synook,Cheers, changed my code accordingly. Amazing, it saves me many, many lines of code and still does what I want. Guess I went around the block few times with this one... Kurt
Link to comment
Share on other sites

Well, the next time you are writing some code and find yourself repeating something lots of times with only minor differences (and especially incremental changes) think "loop!" :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...