Jump to content

Display N/A If The Database Row is Empty


Dragosvr92

Recommended Posts

Hello

i have a SQL db that has some Empty Rows in it ....

I Had used a if else statement to make it display N/A If the answer is different than it should be like this

 

<?php if ($string) {echo $string} else {echo"N/A"};?>

but i think there are better ways of doing this

Can anyone teach me how may i do it?

Edit:

it should be a way to see if the db row value is empty/null :|

 

Link to comment
Share on other sites

Hi Keith

i am not that advanced to PHP To write that code and i know almost Nothing about MySQL :-\ ...

This is the script im using to load the db :

<?php
$db = mysql_connect($server, $username, $password) /*or die(mysql_error())*/;
      mysql_select_db($dbname) /*or die(mysql_error())*/;

$sql = 'SELECT
            country_code ,country_name ,region_code ,region_name ,city ,zipcode ,latitude ,longitude ,	metrocode
         FROM
            ip_group_city
		 WHERE
		 ip_start <=INET_ATON("'.preg_replace("/[^0-9\\.]/", '', $_GET['ip']).'")
         ORDER BY
            ip_start DESC
         LIMIT 0,1';
list($country_code ,$country_name ,$region_code ,$region_name ,$city ,$zipcode ,$latitude ,$longitude ,$metrocode) = mysql_fetch_row(mysql_query($sql));
?>

 

Do you have any idea how may i set that to display N/A On empty rows ? :|

Link to comment
Share on other sites

Hi

 

If a full row is empty then it doesn't exist. I assume you mean a particular field.

 

For example if it was the zip code:-

 

SELECT country_code ,country_name ,region_code ,region_name ,city ,case WHEN zipcode IS NULL THEN "N/A" ELSE zipcode END AS zipcode ,latitude ,longitude , metrocode
FROM ip_group_city
WHERE ip_start <=INET_ATON("'.preg_replace("/[^0-9\\.]/", '', $_GET['ip']).'")
ORDER BY
ip_start DESC
LIMIT 0,1

 

Not tested it so might be a typo but something like that should work.

 

All the best

 

Keith

Link to comment
Share on other sites

umm i attached a image of the empty rows/fields or what they are lol they have a X in the right side

 

i need each of those fields to echo N/A if the field was detected empty when loaded

 

your code didnt worked btw :(

it hasnt displayed N/A it showd nothing

i tried a ip that has the zip code and it showd the zip

 

[attachment deleted by admin]

Link to comment
Share on other sites

Hi

 

Think the code I gave you should have worked. However if the zipcode isn't null (ie, maybe it contains a space) then it will return that value. If you TRIM the zipcode in the case statement then I think that should sort it.

 

SELECT country_code ,country_name ,region_code ,region_name ,city ,case WHEN TRIM(zipcode) IS NULL THEN "N/A" ELSE zipcode END AS zipcode ,latitude ,longitude , case WHEN TRIM(metrocode) IS NULL THEN "N/A" ELSE metrocode END AS metrocode
FROM ip_group_city
WHERE ip_start <=INET_ATON("'.preg_replace("/[^0-9\\.]/", '', $_GET['ip']).'")
ORDER BY
ip_start DESC
LIMIT 0,1

 

Try that.

 

All the best

 

Keith

Link to comment
Share on other sites

it wont work mate... i added that sql code you gave me and i echo the strings zipcode and metrocode and there was nothing to see

 

it only showd the answers when i used another ip that offered those infos :|

 

edit:

there is no space in the field .. its empty .. without any value set

 

ive discussed about this in this forum months ago .... and i couldnt understand wuite well what they were saying >,<

link

Link to comment
Share on other sites

Hi

 

Had a quick play and it seems not to like IS NULL there.

 

Try this:-

 

SELECT country_code ,country_name ,region_code ,region_name ,city ,case WHEN TRIM(zipcode) = '' THEN "N/A" ELSE zipcode END AS zipcode ,latitude ,longitude , case WHEN TRIM(metrocode) = '' THEN "N/A" ELSE metrocode END AS metrocode
FROM ip_group_city
WHERE ip_start <=INET_ATON("'.preg_replace("/[^0-9\\.]/", '', $_GET['ip']).'")
ORDER BY
ip_start DESC

 

All the best

 

Keith

Link to comment
Share on other sites

Hi

 

Different time zones? About 11:30pm here.

 

Think the issue you have had is down to me using single quotes in the SQL, and then you surrounding it with single quotes in php. Change the SQL to double quotes:-

 

SELECT country_code ,country_name ,region_code ,region_name ,city ,case WHEN TRIM(zipcode) = "" THEN "N/A" ELSE zipcode END AS zipcode ,latitude ,longitude , case WHEN TRIM(metrocode) = "" THEN "N/A" ELSE metrocode END AS metrocode
FROM ip_group_city
WHERE ip_start <=INET_ATON("'.preg_replace("/[^0-9\\.]/", '', $_GET['ip']).'")
ORDER BY ip_start DESC

 

All the best

 

Keith

Link to comment
Share on other sites

hi

3:26 AM Here atm .. sleept for an hour lol

 

HOLLY S**T !!! THAT WORKS !! : DD

Thank You Very Much !! :D;)

 

now i would have something else to ask if possible >,<

 

its been discussed here ...

http://forum.ipinfodb.com/viewtopic.php?f=13&t=3557

i have no idea about php classes ... would you have an idea how could i get that sql query loaded in php?

 

Link to comment
Share on other sites

Hi techie

i meant the query from the link from my previous post

 

SELECT tzd.gmtoff, tzd.isdst, tz.name
FROM `timezones_data` tzd
JOIN `timezones` tz ON tz.id = tzd.timezone
WHERE tzd.timezone = (
SELECT `timezone`
FROM `fips_regions`
WHERE `country_code` = 'CA'
AND `code` = '10' )
AND tzd.start < UNIX_TIMESTAMP( now( ) )
ORDER BY tzd.start DESC
LIMIT 1

 

how may i load that with PHP?

 

and usually i mark my topics solved :P

Link to comment
Share on other sites

Hi

 

Looks like you have aready been given the answer in the other thread.

 

Basically the script is setting a few variables (you will need to put your appropriate db name, password, etc in them).

 

Then it connects to the database server, then selects the actual database on there that you want to use.

 

A function is then declared that takes a country code and a region code. A function is a piece of code that (generally) returns a value and is used in several places (it is NOT a class). This function sets up the SQL and submits it to the database. The query returns a result (stored in $query). mysql_fetch_array returns an array of a row from the database, and the function returns this.

 

echo getTimezone('CA', '10'); is called ing the function getTimezone with the parameters 'CA' and '10' and then printing out what is returned (although that wil probably not work as it is returning an array so try print_r(getTimezone('CA', '10'));)

 

All the best

 

Keith

Link to comment
Share on other sites

hi

im lost lol

 

so i put this into a php page and it shows nothing

 

<?error_reporting(0);
$server   = "";		// MySQL hostname
$username = "root";	// MySQL username
$password = "";		// MySQL password
$dbname   = "IPToCountry";	// MySQL db name

$db = mysql_connect($server, $username, $password) /*or die(mysql_error())*/;
      mysql_select_db($dbname) /*or die(mysql_error())*/;

$sql = 'SELECT
            country_code ,country_name ,region_code ,region_name ,city ,zipcode ,latitude ,longitude ,	metrocode
         FROM
            ip_group_city
		 WHERE
		 ip_start <=INET_ATON("79.113.108.166")
         ORDER BY
            ip_start DESC
         LIMIT 0,1';
list($country_code ,$country_name ,$region_code ,$region_name ,$city ,$zipcode ,$latitude ,$longitude ,$metrocode) = mysql_fetch_row(mysql_query($sql));

?>


<?
$server = ''; // MySQL HostName
$username = 'root'; // MySQL UserName
$password = ''; // MySQL Password
$dbname = 'TimeZoneDB';// MySQL db Name

$db = mysql_connect($server, $username, $password) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

function getTimezone($country_code, $region_code) {
$sqltest = "SELECT tzd.gmtoff, tzd.isdst, tz.name
FROM `timezones_data` tzd
JOIN `timezones` tz ON tz.id = tzd.timezone
WHERE tzd.timezone = (
SELECT `timezone`
FROM `fips_regions`
WHERE `country_code` = 'CA'
AND `code` = '10' )
AND tzd.start < UNIX_TIMESTAMP( now( ) )
ORDER BY tzd.start DESC
LIMIT 1";

$query = mysql_query($sqltest, $db);
return mysql_fetch_array($query);
}

echo getTimezone('CA', '10');
print_r(getTimezone('CA', '10'));
?>

Link to comment
Share on other sites

Hi

 

Do you get any error messages, or nothing at all?

 

Try this:-

 

<?php

 

echo "Script Started";

$server = ''; // MySQL HostName

$username = 'root'; // MySQL UserName

$password = ''; // MySQL Password

$dbname = 'TimeZoneDB';// MySQL db Name

 

$db = mysql_connect($server, $username, $password) or die(mysql_error());

mysql_select_db($dbname) or die(mysql_error());

 

function getTimezone($country_code, $region_code)

{

global $db;

$sqltest = "SELECT tzd.gmtoff, tzd.isdst, tz.name

FROM `timezones_data` tzd

JOIN `timezones` tz ON tz.id = tzd.timezone

WHERE tzd.timezone = (

SELECT `timezone`

FROM `fips_regions`

WHERE `country_code` = '$country_code'

AND `code` = '$region_code )

AND tzd.start < UNIX_TIMESTAMP( now( ) )

ORDER BY tzd.start DESC

LIMIT 1";

 

$query = mysql_query($sqltest, $db);

return mysql_fetch_array($query);

}

 

echo getTimezone('CA', '10');

print_r(getTimezone('CA', '10'));

echo "Script Finished";

?>

 

All the best

 

Keith

Link to comment
Share on other sites

nothing at all .....

it only shows this Script StartedScript Finished

 

the timezone db is here if you want to look at it

 

edit

 

Sorry i still had that line to remove errors which i added because the submit form that gives the country_code and region_code

 

this is what it says

 

Script Started

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\IP\Time.php on line 51

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\IP\Time.php on line 51

Script Finished

 

 

line 51 : return mysql_fetch_array($query);

Link to comment
Share on other sites

Thanks :)

 

it is giving

Script Started

Array
(
    [0] => 10800
    [gmtoff] => 10800
    [1] => 1
    [isdst] => 1
    [2] => Europe/Bucharest
    [name] => Europe/Bucharest
)

Script Finished 

 

but it should display something like this 3 fields from this page

 

http://ipinfodb.com/

# Timezone : Europe/Bucharest

# Gmtoffset : 3

# Local time : August 16 09:36:46

Link to comment
Share on other sites

  • 3 weeks later...

Hello

 

if you read the posts you can see how KickStart Done it for me

 

Hi

 

Different time zones? About 11:30pm here.

 

Think the issue you have had is down to me using single quotes in the SQL, and then you surrounding it with single quotes in php. Change the SQL to double quotes:-

 

SELECT country_code ,country_name ,region_code ,region_name ,city ,case WHEN TRIM(zipcode) = "" THEN "N/A" ELSE zipcode END AS zipcode ,latitude ,longitude , case WHEN TRIM(metrocode) = "" THEN "N/A" ELSE metrocode END AS metrocode
FROM ip_group_city
WHERE ip_start <=INET_ATON("'.preg_replace("/[^0-9\\.]/", '', $_GET['ip']).'")
ORDER BY ip_start DESC

 

All the best

 

Keith

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.