Display N/A If The Database Row is Empty

[Dragosvr92]

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 :|

 

[kickstart]

Hi

 

You could use use a case type statement in the SQL to set String to N/A if it is null.

 

All the best

 

Keith

[Dragosvr92]

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 ? :|

[kickstart]

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

[Dragosvr92]

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]

[kickstart]

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

[Dragosvr92]

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

[kickstart]

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

[Dragosvr92]

Hi

Sorry i had to sleep for a bit .. good that you are stil on lol

 

it is giving me this

Parse error: parse error in C:\wamp\www\IP\index.php on line 14

 

line 14 is SELECT country_code ,c...

 

[kickstart]

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

[Dragosvr92]

hi

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

 

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

Thank You Very Much !!

 

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?

 

[MadTechie]

Please mark as solved if this is solved! (bottom left)

 

also what do you mean by

how could i get that sql query loaded in php?

[Dragosvr92]

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

[kickstart]

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

[Dragosvr92]

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'));
?>

[kickstart]

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

[Dragosvr92]

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);

[kickstart]

Hi

 

Just a missing inverted comma:-

 

AND `code` = '$region_code' )

 

All the best

 

Keith

[Dragosvr92]

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

[kickstart]

Hi

 

It has given you some of that info. gmtoff of 10800 is 3 hours but given as seconds. There is nothing in that code to calculate the local time. I presume you need to add or subtract gmtoff to the GMT time to get that.

 

All the best

 

Keith

[newbtophp]

Sorry for bringing up this thread, but how would i set N/A if any of the selected rows are empty? (defining each an every row would be tedious?)

 

Like

SELECT something, another, hey FROM table, IF ANY ROWS (in this case something, another, hey) IS EMPTY THEN DISPLAY N/A

[Dragosvr92]

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