josephbupe

Ok, I got it working after some modifications: if(isset($_POST['upload'])){ $familyname = trim($_POST['familyname']); $firstname = trim($_POST['firstname']); $othernames = trim($_POST['othernames']); $dateofbirth = trim($_POST['$dateofbirth']); $img = trim($_POST['imgurl']); $path=$path.$_FILES['file_upload']['name']; if(move_uploaded_file($_FILES['file_upload']['tmp_name'],$path)) { echo " ".basename($_FILES['file_upload']['name'])." has been uploaded<br/>"; echo '<img src="gallery/'.$_FILES['file_upload']['name'].'" width="80" height="100"/>'; $img=$_FILES['file_upload']['name']; $query="insert into imgtables (familyname,firstname,othernames,dateofbirth,imgurl,date) values('$familyname','$firstname','$othernames',STR_TO_DATE('$dateofbirth','%d-%m-%y'),'$img',now())"; echo($familyname); if($sp->query($query)){ echo "<br/>Inserted to DB also"; }else{ echo "Error <br/>".$sp->error; } } else { echo "There is an error,please retry or ckeck path"; } } I am trying to convert to prepared statement. Regards. Joseph

Hi, The following code was written by someone else. It allows me to upload images to a directory while saving image name in the mysql table. I also want the code to allow me save other data (surname, first name) along with the image name into the table, but my try is not working, only the images get uploaded. What am I missing here? if(isset($_POST['upload'])) { $path=$path.$_FILES['file_upload']['name']; if(move_uploaded_file($_FILES['file_upload']['tmp_name'],$path)) { echo " ".basename($_FILES['file_upload']['name'])." has been uploaded<br/>"; echo '<img src="gallery/'.$_FILES['file_upload']['name'].'" width="48" height="48"/>'; $img=$_FILES['file_upload']['name']; $query="insert into imgtables (fname,imgurl,date) values('$fname',STR_TO_DATE('$dateofbirth','%d-%m-%y'),'$img',now())"; if($sp->query($query)){ echo "<br/>Inserted to DB also"; }else{ echo "Error <br/>".$sp->error; } } else { echo "There is an error,please retry or check path"; } } ?> joseph

Hi, I appreciate this post. My request is if someone can kindly update the code for MySQLi and maybe provide a full working code for someone like myself. Regards. Joseph

Ok, Barand. I will stick to your suggestion. Again thank you. Joseph

Hi Barand, Ok, Thanx. And If understand you well, I will create a foreign key AgencyID in the t_incidents from the t_users table (which also has a foreign key AgencyID from t_agencies). Is that so? Sorry, I just want to be clear on this one, noting also that I slightly departed from my previous table relationship between incidents and agencies after including users table. Joseph

Hi, I want each new incident created into the t_incidents table by the user to be associated with the AgencyID, a foreign key in the t_users table. The problem is I do not know how this relationship will work and whether I will need a junction table. The two tables are: t_Users +----------+----------+------------+ |UserID | AgencyID |User name | +----------+----------+------------+ |1 | 1 |john | +----------+----------+------------+ |2 | 1 |andrew | +----------+----------+------------+ t_Agencies +----------+------------+ |AgencyID |agency name | +----------+------------+ |1 |police | +----------+------------+ |2 |immigration | +----------+------------+ I will appreciate your advice. Joseph

Thank you Mac. It worked.

Hi, I am trying to migrate my project from mysql to mysqli but with little knowledge of the later. Among the noticeable errors is that the code is no longer fetching data from the database, and I am also getting UNDEFINED VARIABLE errors on the in the mysqli statement that should be binding results to variables. Here is the code if you can help: //set connection variables $host = "localhost"; $username = "root"; $password = "password"; $db_name = "mydb"; //database name //connect to mysql server $mysqli = new mysqli($host, $username, $password, $db_name); //check if any connection error was encountered if(mysqli_connect_errno()) { echo "Error: Could not connect to database."; exit; } //Just counting ...required for pagination... $query = "SELECT COUNT(*) FROM t_persons"; $result = mysqli_query($mysqli,$query) or die(mysqli_connect_errno()); $num_rows = mysqli_fetch_row($result); $pages = new Paginator; $pages->items_total = $num_rows[0]; $pages->mid_range = 9; // Number of pages to display. Must be odd and > 3 $pages->paginate(); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } if ($stmt = $mysqli->prepare("SELECT p.PersonID, p.ImagePath, p.FamilyName, p.FirstName, p.OtherNames, p.Gender, p.CountryID, p.StatusID, i.IncidentDate, i.IncidentCountryID, i.AgencyID, i.KeywordID FROM t_incidents i INNER JOIN t_incident_persons ip ON ip.IncidentID = i.IncidentID INNER JOIN t_persons p ON ip.PersonID = p.PersonID WHERE p.PersonID>0" . $likes . " ORDER BY p.PersonID DESC $pages->limit")) { /* Execute the prepared Statement */ $stmt->execute(); /* Bind results to variables */ $stmt->bind_result($PersonID,$ImagePath,$FamilyName,$FirstName,$OtherNames,$Gender,$CountryID,$StatusID,$IncidentDate,$IncidentCountryID,$AgencyID,$KeywordID); /* fetch values */ while ($rows = $stmt->fetch()) { // display records in a table // $PersonID=$row[0]; ?> <div class="thumbnail"> <a href=details.php?PersonID=<?php echo $PersonID ?> target=gallery><img src="./Persons_Images/<?php echo $row[1]; ?>" width="100" height="130" alt="" /></a><br> <a href="details.php?PersonID=<?php echo $PersonID; ?>"> <?php echo $row[2]; ?> <?php echo $row[3]; ?></a> </div> <?php }?> <?php } /* Close the statement */ $stmt->close(); /* close our connection */ $mysqli->close(); ?> I will appreciate. Joseph

Looking into it. Thanx alot.

Hi, First, I wish to apologize for taking too long to acknowledge your responses to my earlier question above. The suggested code did not return records between specified date range. Here is the query echo: SELECT * FROM t_persons WHERE PersonID>0 AND IncidentDate BETWEEN '' AND '' ORDER BY p.PersonID DESC LIMIT 0,27 For clarification purpose, I want to know what these variables refer to: 1. $start 2. $end I assume that: start_date is the name of the field to input the first date end_date is the name of the field to input the second date I appreciate your further assistance. Joseph

That's what I needed.

Thanx, it's working.

Hi, How can I select multiple columns from two tables and run a search through multiple fields? My tables are: t_persons (holds information about persons) t_incidents (holds foreign keys from other tables including t_persons table) What I want is to pull some columns from the two tables above and run a search with a LIKE criteria, something like below. The code originally worked well with only one table, but for two tables it generate errors: $query = "SELECT p.PersonID ,p.ImagePath ,p.FamilyName ,p.FirstName ,p.OtherNames ,p.Gender ,p.CountryID ,i.IncidentDate ,i.KeywordID ,i.IncidentCountryID ,i.StatusID FROM t_incidents AS i LEFT JOIN t_persons AS p ON i.PersonID = p.PersonID WHERE FamilyName LIKE '%" . $likes . "%' AND FirstName LIKE '%" . $likes . "%' AND OtherNames LIKE '%" . $likes . "%' AND Gender LIKE '%" . $likes . "%' AND IncidentDate LIKE '%" . $likes . "%' AND KeywordID LIKE '%" . $likes . "%' AND IncidentCountryID LIKE '%" . $likes . "%' AND StatusID LIKE '%" . $likes . "%' ORDER BY PersonID DESC $pages->limit"; Errors are: Column 'IncidentDate' in where clause is ambiguous Column 'KeywordID' in where clause is ambiguous Column 'IncidentCountryID' in where clause is ambiguous Column 'StatusID' in where clause is ambiguous These columns are foreign keys on t_incidents table. I have also attached the table relationship diagram if it helps. I will appreciate any better way to do this. Thanx. Joseph

Sorry here is the screenshort of the EER diagram. Regards. Joseph

Hi, I am re-visiting my database table relationship for criminal incidents. Six tables are involved plus a junction table to make it seven tables in total. I need someone to review this relationship and advise whether or not it is correctly set up. The relationships are as follows: 1. A person can belong to more than one incident: t_persons (one-to-many) t)_incidents_persons 2. A person can only have one nationality by birth t_persons (one-to-one) t_countries 3. An agency can have more than one incident t_agencies (one-to-many) t_incidents 4. A status (e.g. closed incident) can belong to more than one incident t_status (one-to-many_ t_incidents 5. A keyword (i.e. offence type eg theft) t_offencekeywords (one-to-many) t_incidents

Yes, it's what I was looking for. Thanx alot.

Resolved. The issue was with the margin settings. Regards

Hi, I am trying to align sections of my CSS layout side-by-side, but I am having problems with it. I expect the left sidebar (#aside) with a 30% width to meet with the content section (#content) of the width of 70%. But, the changes I make are not translating as expected. See attached screenshort of what is happening and the CSS code bellow: #container { margin: 0 auto; width: 800px; background: #fff; } #header { background: #5a83c3; padding: 20px; } #header h1 { margin: 0; } #navigation { float: left; width: 100%; background: #d1dceb; border-top: 1px solid #fff; } #navigation ul { margin: 0; padding: 0; } #navigation ul li { list-style-type: none; display: inline; } #navigation li a { display: block; float: left; padding: 5px 10px; color: #fff; text-decoration: none; border-right: 1px solid #fff; } #navigation li a:hover { background: #383; } #content-container { float: right; width: 100%; background: #FFF url(layout-two-liquid-background.gif) repeat-y 68% 0; } #content { clear: right; float: right; width: 70%; padding: 10px 0; margin: 0 0 0 4%; display: inline; background: #dfe6ef; border-top: 1px solid #fff; border-left: 1px solid #fff; border-bottom: 1px solid #fff; } #content h2 { margin: 0; } #aside { float: left; width: 20%; padding: 10px 0; margin: 0 3% 0 0; display: inline; background: #d1dceb; border-top: 1px solid #fff; } #aside h3 { margin: 0; } #footer { clear: right; background: #d1dceb; text-align: right; padding: 20px; width: 68%; float: right; overflow: hidden; } #footer p.left { float: left; text-align: left; margin-left: 5px; } #footer p.right { float: right; text-align: right; margin-right: 5px; }

Hi, I am looking for a tutorial or code sample I can use to learn MySQLi pagination with an option to select items per page, like the attached screenshort. Any help is grately appreciated. Regards. joseph

Hi, I am developing a web application which will be comprised of three columns: (1) Search form, (2) Image gallery, (3) Image details. The site is going to be more like html framesets, but research reveals that framesets are not favourable due to many reasons. I however found an alternative that I want to use instead of html framesets http://www.ironspider.ca/frames/smartframes.htm I thought it would be easy for me to add rows which should serve as a page header, but I was wrong. I tried adding to this part of the code, but row apeared in a wrong place at the bottom: // Writes frameset document.write('<frameset rows="17%,*" frameborder="no">'); document.write('<frameset cols="25%,75%" frameborder="no">'); document.write('<frame src="' + menuURL + '" name="menu">'); document.write('<frame src="' + contentURL + '" name="content" marginheight="15" marginwidth="50" scrolling="yes">'); // --> The original script for the index.html page is as follows: <!-- /* SmartFrames - Author: Robert Darrell - http://www.ironspider.ca/ */ // Defines variables // REPLACE menu.htm WITH YOUR MENU PAGE // REPLACE page1.htm WITH YOUR INITIAL CONTENT PAGE menuURL = "menu.htm"; contentURL = "page1.htm"; reloadURL = parent.document.URL; orphanURL = reloadURL.substring(reloadURL.indexOf('?')+1, reloadURL.indexOf('&')); frameName = reloadURL.substring(reloadURL.indexOf('&')+1, reloadURL.length); // Reassigns variable according to frame name passed by orphan if (frameName == "menu") { menuURL = orphanURL; } else if (frameName == "content") { contentURL = orphanURL; } // Writes frameset document.write('<frameset cols="25%,75%" frameborder="no">'); document.write('<frame src="' + menuURL + '" name="menu">'); document.write('<frame src="' + contentURL + '" name="content" marginheight="15" marginwidth="50" scrolling="yes">'); // --> </script> <!-- Use the noframes section to help search engines index your site --> <noframes>THIS WEBSITE IS ABOUT...<br><br> <a href="menu.htm">Site Menu</a> </noframes> </frameset> I hope someone will help. Regards. joseph

Hi, I want to restructure my database tables so that I can have one table (t_incidents) to hold foreign keys instead of holding foreign keys in the table "t_persons" because one person can commit more than one offense. However, I need to know how the join should be implemented with MySQLi prepared statement. I need some one to review the following statement for me and advise: if ($stmt = $mysqli->prepare("Select t_persons.PersonID ,t_persons.FamilyName ,t_persons.FirstName ,t_persons.OtherNames ,t_persons.Gender ,t_persons.CountryID ,t_persons.ImagePath ,t_incidents.IncidentID ,t_incidents.Incident ,t_incidents.IncidentDate ,t_incidents.PersonID ,t_incidents.CountryID ,t_incidents.OffenceKeywordID ,t_incidents.StatusID ,t_incidents.AgencyID ,t_status.StatusID ,t_status.Status ,t_offencekeyword.KeywordID ,t_offencekeyword.Keyword ,t_countries.CountryID ,t_countries.Country ,t_agencies.AgencyID ,t_agencies.Agency From t_persons Inner Join t_incidents On t_persons.PersonID = t_incidents.PersonID Inner Join t_incidents On t_countries.CountryID = t_incidents.CountryID Inner Join t_incidents On t_status.StatusID = t_incidents.StatusID Inner Join t_incidents On t_offenceskeyword.KeywordID = t_incidents.KeywordID Inner Join t_incidents On t_agencies.AgencyID = t_incidents.AgencyID Where t_persons.PersonID = '$PersonID'")) { Regards. josephbupe

Ok, now I need help to merge the following two pieces of code into a single block for my search criteria. The two codes are both IF statements and I am lost on how to make them work as a whole. Actually, I have been using the first, but the second has been suggested to me: $criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' ); $likes = ""; $url_criteria = ''; foreach ( $criteria AS $criterion ) { if ( ! empty($_POST[$criterion]) ) { $value = ($_POST[$criterion]); $likes .= " AND `$criterion` LIKE '%$value%'"; $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]); } elseif ( ! empty($_GET[$criterion]) ) { $value = mysql_real_escape_string($_GET[$criterion]); $likes .= " AND `$criterion` LIKE '%$value%'"; $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]); } //var_dump($likes); } $sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC"; And the date range criteria code: if (!empty($_REQUEST['start_date']) && !empty($_REQUEST['end_date'])) { $start = mysqli_real_escape_string($con, $_REQUEST['start_date']); $end = mysqli_real_escape_string($con, $_REQUEST['end_date']); $likes .= " AND IncidentDate BETWEEN '$start' AND '$end'"; $url_criteria .= '&start_date='.htmlentities($_REQUEST['start_date']).'&end_date='.htmlentities($_REQUEST['end_date']); }

Hi, I can I include a date range criteria to query with in the following code? The date field in the table (t_persons) is IncidentDate. $criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' ); $likes = ""; $url_criteria = ''; foreach ( $criteria AS $criterion ) { if ( ! empty($_POST[$criterion]) ) { $value = ($_POST[$criterion]); $likes .= " AND `$criterion` LIKE '%$value%'"; $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]); } elseif ( ! empty($_GET[$criterion]) ) { $value = mysql_real_escape_string($_GET[$criterion]); $likes .= " AND `$criterion` LIKE '%$value%'"; $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]); } //var_dump($likes); } $sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC"; Kind regards.

Hi all, I wish I could use the mysqli/ PDO to accomplish what I am trying to do right away. I do not know either how I can intergrate the "=" criteria with the "LIKE" in the following piece of code. The code is for searching with multiple criterion. The search with this cat_id is okey expect that I want its criteria not to be part of the "LIKE" criteria; instead it should be "=" so that I can get the exact match in the search, since cat_id is saved as a foreign key in the child table. May be some form of switch but how? $criteria = array('ctitle', 'csubject', 'creference', 'cat_id', 'cmaterial', 'ctechnic', 'cartist', 'csource', 'stolen'); $likes = ""; $url_criteria = ''; foreach ( $criteria AS $criterion ) { if ( ! empty($_POST[$criterion]) ) { $value = ($_POST[$criterion]); $likes .= " AND `$criterion` LIKE '%$value%'"; $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]); } elseif ( ! empty($_GET[$criterion]) ) { $value = mysql_real_escape_string($_GET[$criterion]); $likes .= " AND `$criterion` LIKE '%$value%'"; $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]); } //var_dump($likes); } $sql = "SELECT * FROM collections WHERE c_id>0" . $likes . " ORDER BY c_id ASC"; Your help will be appreciated.

Resolved. Thanx