Solved

php How to make & split array then rebuild with extra text from mysql id

Posted on 2014-01-13
9
399 Views
Last Modified: 2014-03-27
i have some ids in a table iwia_events that holds the id of events. i need to be able to search another table for all the id=X (X being the id mentioned)
i currently have
 $sql=mysql_query("select * from iwia_events WHERE author_id=".$_SESSION['user_id']."");
$row['id'];

-------------------------------------

   //show certian pages
    $words = array( 'id=5','id=72' );
    //$words = array( ''); //all
$whereClause = '';
foreach( $words as $word) {
   $whereClause .= ' query_string LIKE "%' . $word . '%" OR';
}

// Remove last 'OR'
$whereClause = substr($whereClause, 0, -2);



    $sql="select page_name,query_string,count(DISTINCT ip_address) as page_count
      from visitor_tracking
      WHERE" . $whereClause ."
      AND MONTH(`timestamp`) = ".date('n')."
      AND YEAR(`timestamp`)= ".date('Y')."
      group by page_name,query_string;
      ";

Open in new window


im open to any solution. this is what i came up with but am unsure of how to do it.

can i either make a join query with both tables or do i rebuild the $words = array( 'id=5','id=72' ) with something like split and join with id= added to array

i do not know how to join the tables nor how to add the id= to an array or how to make an array with the ids form the select query

recap: im looking to take a lists of author id matches and have my second table return the ids from first table in a field of query_string that holds the id=(id number) as a string example id=5 or id=72 so it be id= iwia_events.author_id, etc

thank you in advance for any code or help you may provide.
0
Comment
Question by:Johnny
  • 7
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39777360
Can you please post the CREATE TABLE statements for the iwia_events and visitor_tracking tables?  Thanks.
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 39777367
1. I would suggest parsing out the ID from the query string and storing it separately. Doing a text search for that is going to be inefficent and likely inaccurate. For example, if you want to search for query_string LIKE '%id=123%', then you'll not only get:

id=123

..but also anything that starts with 1:
id=12345

This can be mitigated somewhat if the id parameter is ALWAYS at the end of the query_string field:

query_string LIKE '%id=123' (no ending wildcard %)

...or if there's ALWAYS another parameter afterwards:

query_string LIKE '%id=123&%' (& "finishes" the number, then we have the rest of the wildcard %)

If you parse out the ID ahead of time, then you can index the field for faster searching and there's no messing with wildcards and text searches (and you can then join as necessary).

2. Your query at the end does a DISTINCT on the IP address, but if you're trying to track unique visitors, it's better to use sessions and store the session ID in the database, and then look for unique combinations of timestamp and session ID.
0
 

Author Comment

by:Johnny
ID: 39777399
@Ray Paseur²
visitor_tracking
CREATE TABLE IF NOT EXISTS `visitor_tracking` (
  `entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `visitor_id` int(11) DEFAULT NULL,
  `ip_address` varchar(15) NOT NULL,
  `page_name` text,
  `query_string` text,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`entry_id`),
  KEY `visitor_id` (`visitor_id`,`timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=42 ;

Open in new window


iwia_events
CREATE TABLE IF NOT EXISTS `iwia_events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_title` varchar(255) NOT NULL DEFAULT '',
  `event_location` varchar(255) DEFAULT NULL,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `type` text NOT NULL COMMENT 'used for google map icon',
  `event_price` varchar(20) DEFAULT NULL,
  `event_image` varchar(255) DEFAULT 'http://completelocal.info/images/iwia/default-event-avatar.png',
  `event_url` varchar(200) DEFAULT NULL,
  `event_start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `event_end` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `event_text` text,
  `extra_info` text,
  `facebook` enum('0','1') NOT NULL DEFAULT '0',
  `twitter` enum('0','1') NOT NULL DEFAULT '0',
  `active` enum('0','1') NOT NULL DEFAULT '1',
  `categories` text NOT NULL,
  `tags` varchar(359) NOT NULL,
  `date_published` int(10) NOT NULL,
  `author_id` int(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75 ;

Open in new window

0
 

Author Comment

by:Johnny
ID: 39777420
@ gr8gonzo

thanks for the reply.

as for ip versus other tracking methods i was going to use piwik and Ray suggested Google Analytics it was apparent at that time that i need to read up on how to get unique page views and total page hits for a passed to a php script via  a variable page stats - i set out to at least count something for now till i can devote more time to getting better accuracy. with ips i figure i'll get a min number for now.

as for the id= yes i realized that % wildcard would not return correctly as i was testing and didn't get back to this to ask that too

again im very open here
my table for the second one right now hold info like (live example)
INSERT INTO `visitor_tracking` (`entry_id`, `visitor_id`, `ip_address`, `page_name`, `query_string`, `timestamp`) VALUES
(32, 1, '98.28.197.19x', '/single_event_page.php', 'id=5', '2014-01-10 19:18:18'),
(33, 1, '98.28.197.19x', '/single_event_page.php', 'id=2', '2014-01-10 19:31:49'),
(34, 1, '98.28.197.19x', '/single_event_page.php', 'id=73', '2014-01-10 20:12:27'),
(35, 1, '98.28.197.19x', '/single_event_page.php', 'id=72', '2014-01-10 20:25:12'),
(36, 2, '66.220.158.11x', '/single_event_page.php', 'id=72', '2014-01-10 20:50:31'),
(37, 3, '69.77.153.10x', '/single_event_page.php', 'id=72', '2014-01-10 21:07:39'),
(38, 4, '98.28.200.22x', '/single_event_page.php', 'id=2', '2014-01-10 21:34:55'),
(39, 5, '50.179.200.11x', '/single_event_page.php', 'id=72', '2014-01-10 23:08:51'),
(40, 6, '180.76.6.14x', '/single_event_page.php', 'id=28', '2014-01-11 02:32:31'),
(41, 7, '180.76.5.11x', '/single_event_page.php', 'id=20', '2014-01-12 15:06:17');

Open in new window


im paring my hits for the page name as and example id=5

im very open to any and all suggestions
for reference this was the first question that prompted this post and way of doing this
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28335270.html

and this is the second question on how i derived at what it is now
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28335642.html
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Johnny
ID: 39782855
i dont know why i can find something so simple and muck it up so badly but anyways this is still eluding me on how to get this all to work.

im now trying to see if i can make a array of the event id's i need from the author_id field search testing for author_id 1 should return 73 and 74 (i can do this in myphpadmin with
select id from iwia_events WHERE author_id= 1

Open in new window


i made a code block to do just that using mysqli (i keep forgetting mysql is out now)
and im getting an error in my code
Fatal error: Call to undefined method mysqli_stmt::fetch_assoc() in /home/local/public_html/ip_report.php on line 51
line 51 is: (see attached code as line 13)
while ($row = $stmt->fetch_assoc()) {

heres my code im trying to do the first part with (to make an array to check for event ids in this post. as i said im blind here and still not getting how to make this work. hence this post. but im attempting to try and make it work

my code:
function a_id($id, $mysqli){
  $userid = "SELECT id FROM iwia_events WHERE id = ? ";
  if($stmt = $mysqli->prepare($userid)) 
  {
    $stmt->bind_param('i', $id);
    $stmt->execute();
    if(!$stmt->execute()) 
        { 
            echo $stmt->error.' in query: '.$userid; 
        }
    else {
        $author_id_events = array();
        while ($row = $stmt->fetch_assoc()) {
          $author_id_events[] = $row;
        }
        $stmt->close(); 
        return $author_id_events;
    }
    $stmt->close();
  }
}
 
 
 include ("mysqli_ctx.php");
 
 $author_id_events_found =a_id($author_id,$mysqli);

 
 echo "<pre>";
 var_dump($author_id_events_found);
 echo "</pre>";
 

Open in new window


as you can see im trying to work this out and im still no closer and have not solved this yet
0
 

Author Comment

by:Johnny
ID: 39782870
oh btw as per gr8gonzo i changed my code to now be

    $words = array( 'id=5','id=72','id=73' );
    //$words = array( ''); //all
$whereClause = '';
foreach( $words as $word) {
   $whereClause .= ' query_string = "' . $word . '" OR';
}

// Remove last 'OR'
$whereClause = substr($whereClause, 0, -2);

Open in new window


this no longer relies on wildcards and directly looks for the id=#

im still unsure if this is the right path and im still unsure how to build the $words array from my $author_id_events_found hence this original question but im trying to work it out, so far not able too i keep hitting problems
0
 

Author Comment

by:Johnny
ID: 39783171
ok one thing done..still need to look for ids in query_string field
but heres the way to get the event_id from author id

 include ("mysqli_ctx.php");
 
$query = "SELECT id FROM iwia_events WHERE author_id =".$author_id." ORDER by id";
echo "query:  ".$query."<br>";
$result = $mysqli->query($query);

while($row = $result->fetch_array())
{
$rows[] = $row;
}

foreach($rows as $row)
{
$author_id_events_found .=$row['id'].", ";
}

/* free result set */
$result->close();

/* close connection */
$mysqli->close();
 
$author_id_events_found = substr($author_id_events_found, 0, -2); 
 echo "<pre>";
 var_dump($author_id_events_found);
 echo "</pre>";
 
 
echo "event ids ".$author_id_events_found."<br>";
 die();

Open in new window

0
 

Accepted Solution

by:
Johnny earned 0 total points
ID: 39783240
got it....
this seams to work not sure if its the best way, i really wanted to know if this is the right way or not.

    $words = explode(", ", $author_id_events_found);

    $whereClause = '';
foreach( $words as $word) {
   $whereClause .= ' query_string = "' . "id=".$word . '" OR';
}

// Remove last 'OR'
$whereClause = substr($whereClause, 0, -2);

Open in new window


ill wait a few days to close this to see if there any comments on if this s the right way or not. i would think a join table query would be better. but i have not played with that very much. so im unsure how to use queries with multiple tables

awaiting to see if anyone replies with comments.
0
 

Author Closing Comment

by:Johnny
ID: 39958357
this was the solution
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now