Member Avatar for DaveyMoyes

Hi Everyone, I have been trying to understand mysql a little further - with some small steps in the right direction.

but I have come across a problem I am unable to fix / get working.

I have a form, that has a variable $country -

I am trying to get the sms text message price for the varible $country

Below is my attempt - to get the sms price

(table field name (price) from tbl (sms_prices))
(table field name (name) from tbl (sms_countries))

$query = "select c.country_id, c.name, p.price
 		from sms_countries c, sms_prices p
 	where
  		c.name = '$country' and
  		c.country_id = p.country_id";	 
			
			$result = mysql_query($query) or die(mysql_error());
			while($row = mysql_fetch_array($result)){
			$smsprice = $row['price'];		
	}

Hope someone can point me in the righr direction and explain where I am going wrong.

Thanks in advance

  • 2 Contributors
  • 2 Replies
  • 287 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by DaveyMoyes

Dani AI

Generated

Good catch by — the symptom (no rows returned) matched a data problem, not the SQL. As said, the query itself looked fine; the usual culprits are typos, hidden characters, or case/collation differences when you compare on a human-readable field like a name.

Short diagnostic checklist

  • Run the same SELECT directly in phpMyAdmin or the MySQL client to see results immediately.
  • Echo or log the actual $country value sent to MySQL (show surrounding delimiters) to catch trailing/leading spaces.
  • Check for hidden characters with SELECT name, LENGTH(name), HEX(name) FROM sms_countries WHERE name LIKE '…'. Length/HEX often reveals extra bytes.
  • Try a fuzzy search (LIKE '%part%' or SOUNDEX(name) = SOUNDEX('value')) to locate near-matches.

Practical fixes and safer workflow

  • Use the numeric country_id in forms/URLs instead of a name — integers are stable and avoid spelling problems.
  • Enforce referential integrity: a UNIQUE index on sms_countries.name and a FOREIGN KEY (InnoDB) from sms_prices.country_id prevents mismatches.
  • Move off deprecated mysql_* functions; use prepared statements (PDO or mysqli) to avoid SQL injection and accidental quoting bugs. Example PDO pattern:
$stmt = $pdo->prepare(
  "SELECT p.price
   FROM sms_prices p
   JOIN sms_countries c ON c.country_id = p.country_id
   WHERE c.name = :country
   LIMIT 1"
);
$stmt->execute(['country' => $country]);
$price = $stmt->fetchColumn();

Final tips
Normalize stored values (store a trimmed/lowercase canonical column), log queries during debugging (don’t use die() in production), and add a quick admin check that lists country names and lengths — it saves hours hunting typos like this one.

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

Your query looks just fine to me. What is the problem you have with it? Have you tried it in phpMyAdmin?

Edited by pritaeas because: n/a
Member Avatar for DaveyMoyes

Hi, I have found the issue, country name was incorrect in table - Typo that has cost me 4 hours of head scratching - :) Grrrrrrrrrrrrrrrr

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.